Skip Headers

Oracle® Database SQL Reference 10g Release 2 (10.2) Part Number B14200-02 |
Home | Book List | Contents | Index | Master Index | Feedback

Previous | Next
| View PDF

# ALTER TABLE

Purpose

Use the `ALTER` `TABLE` statement to alter the definition of a nonpartitioned table, a partitioned table, a table partition, or a table subpartition. For object tables or relational tables with object columns, use `ALTER` `TABLE` to convert the table to the latest definition of its referenced type after the type has been altered.

See Also: CREATE TABLE for information on creating tables Oracle Text Reference for information on `ALTER` `TABLE` statements in conjunction with Oracle Text

Additional Topics:

- Prerequisites

- Syntax

- Semantics

- Examples

Prerequisites

The table must be in your own schema, or you must have `ALTER` object privilege on the table, or you must have `ALTER` `ANY` `TABLE` system privilege.

Additional Prerequisites for Partitioning Operations If you are not the owner of the table, then you need the `DROP` `ANY` `TABLE` privilege in order to use the `drop_table_partition` or `truncate_table_partition` clause.

You must also have space quota in the tablespace in which space is to be acquired in order to use the `add_table_partition`, `modify_table_partition`, `move_table_partition`, and `split_table_partition` clauses.

Additional Prerequisites for Constraints and Triggers To enable a unique or primary key constraint, you must have the privileges necessary to create an index on the table. You need these privileges because Oracle Database creates an index on the columns of the unique or primary key in the schema containing the table.

To enable or disable triggers, the triggers must be in your schema or you must have the `ALTER` `ANY` `TRIGGER` system privilege.

Additional Prerequisites When Using Object Types To use an object type in a column definition when modifying a table, either that object must belong to the same schema as the table being altered, or you must have either the `EXECUTE` `ANY` `TYPE` system privilege or the `EXECUTE` schema object privilege for the object type.

See Also: CREATE INDEX for information on the privileges needed to create indexes

Syntax

alter_table::=

Description of the illustration alter_table.gif

Note: You must specify some clause after `table`. That is, none of the clauses after `table` are required, but you must specify at least one of them.

Groups of ALTER TABLE syntax:

- alter_table_properties::=

- column_clauses::=

- constraint_clauses::=

- alter_table_partitioning ::=

- alter_external_table_clauses ::=

- move_table_clause ::=

- enable_disable_clause::=

After each clause you will find links to its component subclauses.

alter_table_properties::=

Description of the illustration alter_table_properties.gif

(physical_attributes_clause::=, logging_clause::=, table_compression ::=, supplemental_table_logging ::=, allocate_extent_clause ::=, deallocate_unused_clause::= , shrink_clause::=, upgrade_table_clause ::=, records_per_block_clause ::=, parallel_clause::=, row_movement_clause::=, alter_iot_clauses::=)

physical_attributes_clause::=

Description of the illustration physical_attributes_clause.gif

(storage_clause::=)

logging_clause::=

Description of the illustration logging_clause.gif

table_compression ::=

Description of the illustration table_compression.gif

supplemental_table_logging ::=

Description of the illustration supplemental_table_logging.gif

supplemental_log_grp_clause::=

Description of the illustration supplemental_log_grp_clause.gif

supplemental_id_key_clause::=

Description of the illustration supplemental_id_key_clause.gif

allocate_extent_clause ::=

Description of the illustration allocate_extent_clause.gif

(size_clause::=)

deallocate_unused_clause::=

Description of the illustration deallocate_unused_clause.gif

(size_clause::=)

shrink_clause::=

Description of the illustration shrink_clause.gif

upgrade_table_clause ::=

Description of the illustration upgrade_table_clause.gif

(column_properties::=)

records_per_block_clause ::=

Description of the illustration records_per_block_clause.gif

parallel_clause::=

Description of the illustration parallel_clause.gif

row_movement_clause::=

Description of the illustration row_movement_clause.gif

alter_iot_clauses::=

Description of the illustration alter_iot_clauses.gif

(alter_overflow_clause ::=, alter_mapping_table_clauses ::=)

index_org_table_clause ::=

Description of the illustration index_org_table_clause.gif

mapping_table_clauses::=

Description of the illustration mapping_table_clauses.gif

key_compression::=

Description of the illustration key_compression.gif

index_org_overflow_clause::=

Description of the illustration index_org_overflow_clause.gif

(segment_attributes_clause::=)

segment_attributes_clause::=

Description of the illustration segment_attributes_clause.gif

(physical_attributes_clause::=, logging_clause::=)

alter_overflow_clause ::=

Description of the illustration alter_overflow_clause.gif

(segment_attributes_clause::=, allocate_extent_clause ::=, shrink_clause::=, deallocate_unused_clause::=)

add_overflow_clause::=

Description of the illustration add_overflow_clause.gif

(segment_attributes_clause::=)

alter_mapping_table_clauses ::=

Description of the illustration alter_mapping_table_clauses.gif

(allocate_extent_clause ::=, deallocate_unused_clause::=)

column_clauses::=

Description of the illustration column_clauses.gif

(add_column_clause ::=, modify_column_clauses::=, drop_column_clause ::=, rename_column_clause ::=, modify_collection_retrieval ::=, modify_LOB_storage_clause::=, alter_varray_col_properties::=, encryption_spec::=)

add_column_clause ::=

Description of the illustration add_column_clause.gif

(column_definition::=, column_properties::=)

column_definition::=

Description of the illustration column_definition.gif

(encryption_spec::=, `inline_constraint` and `inline_ref_constraint`: constraint::=)

modify_column_clauses::=

Description of the illustration modify_column_clauses.gif

modify_col_properties::=

Description of the illustration modify_col_properties.gif

(encryption_spec::=, `inline_constraint`: constraint::=, LOB_storage_clause::=)

encryption_spec::=

Description of the illustration encryption_spec.gif

modify_col_substitutable::=

Description of the illustration modify_col_substitutable.gif

drop_column_clause ::=

Description of the illustration drop_column_clause.gif

rename_column_clause ::=

Description of the illustration rename_column_clause.gif

modify_collection_retrieval ::=

Description of the illustration modify_collection_retrieval.gif

constraint_clauses::=

Description of the illustration constraint_clauses.gif

`(constraint_state`: constraint::=)

drop_constraint_clause::=

Description of the illustration drop_constraint_clause.gif

column_properties::=

Description of the illustration column_properties.gif

object_type_col_properties::=

Description of the illustration object_type_col_properties.gif

substitutable_column_clause::=

Description of the illustration substitutable_column_clause.gif

nested_table_col_properties::=

Description of the illustration nested_table_col_properties.gif

object_properties::=

Description of the illustration object_properties.gif

(`inline_constraint`, `inline_ref_constraint`, `out_of_line_constraint`, `out_of_line_ref_constraint`: constraint::=)

supplemental_logging_props::=

Description of the illustration supplemental_logging_props.gif

(supplemental_log_grp_clause::=, supplemental_id_key_clause::=)

physical_properties::=

Description of the illustration physical_properties.gif

(segment_attributes_clause::=, index_org_table_clause ::=, external_data_properties::=)

varray_col_properties::=

Description of the illustration varray_col_properties.gif

(substitutable_column_clause::=)

LOB_storage_clause::=

Description of the illustration LOB_storage_clause.gif

LOB_parameters::=

Description of the illustration LOB_parameters.gif

(storage_clause::=, logging_clause::=)

modify_LOB_storage_clause::=

Description of the illustration modify_LOB_storage_clause.gif

modify_LOB_parameters::=

Description of the illustration modify_LOB_parameters.gif

(storage_clause::=, logging_clause::=, allocate_extent_clause ::=, shrink_clause::=, deallocate_unused_clause::=)

alter_varray_col_properties::=

Description of the illustration alter_varray_col_properties.gif

LOB_partition_storage ::=

Description of the illustration LOB_partition_storage.gif

(LOB_storage_clause::=, varray_col_properties::=)

XMLType_column_properties::=

Description of the illustration XMLType_column_properties.gif

XMLType_storage::=

Description of the illustration XMLType_storage.gif

XMLSchema_spec::=

Description of the illustration XMLSchema_spec.gif

alter_external_table_clauses ::=

Description of the illustration alter_external_table_clauses.gif

(add_column_clause ::=, modify_column_clauses::=, drop_column_clause ::=, drop_constraint_clause::=, parallel_clause::=)

external_data_properties::=

Description of the illustration external_data_properties.gif

alter_table_partitioning ::=

Description of the illustration alter_table_partitioning.gif

(modify_table_default_attrs ::=, set_subpartition_template ::=, modify_table_partition::=, modify_table_subpartition::=, move_table_partition ::=, move_table_subpartition::=, add_table_partition::=, coalesce_table_partition::=, drop_table_partition::=, drop_table_subpartition ::=, rename_partition_subpart::=, truncate_partition_subpart::=, split_table_partition::=, split_table_subpartition ::=, merge_table_partitions ::=, merge_table_subpartitions ::=, exchange_partition_subpart ::=

modify_table_default_attrs ::=

Description of the illustration modify_table_default_attrs.gif

(segment_attributes_clause::=, table_compression ::=, key_compression::=, LOB_parameters::=, alter_overflow_clause ::=)

set_subpartition_template ::=

Description of the illustration set_subpartition_template.gif

(list_values_clause::=, partitioning_storage_clause::=)

modify_table_partition::=

Description of the illustration modify_table_partition.gif

(modify_range_partition::=, modify_hash_partition::=, modify_list_partition::=)

modify_range_partition::=

Description of the illustration modify_range_partition.gif

(partition_attributes::=, update_index_clauses::=, alter_mapping_table_clauses ::=)

modify_hash_partition::=

Description of the illustration modify_hash_partition.gif

(partition_attributes::=, add_hash_subpartition ::=, parallel_clause, alter_mapping_table_clauses ::=)

modify_list_partition::=

Description of the illustration modify_list_partition.gif

(partition_attributes::=, add_list_subpartition::=)

modify_table_subpartition::=

Description of the illustration modify_table_subpartition.gif

(modify_hash_subpartition::=, modify_list_subpartition ::=)

move_table_partition ::=

Description of the illustration move_table_partition.gif

(table_partition_description::=, update_index_clauses::=, parallel_clause::=)

move_table_subpartition::=

Description of the illustration move_table_subpartition.gif

(subpartition_spec::=, update_index_clauses::=, parallel_clause::=)

add_table_partition::=

Description of the illustration add_table_partition.gif

add_range_partition_clause::=

Description of the illustration add_range_partition_clause.gif

(range_values_clause::=, table_partition_description::=, update_index_clauses::=)

add_hash_partition_clause ::=

Description of the illustration add_hash_partition_clause.gif

(partitioning_storage_clause::=, update_index_clauses::=, parallel_clause::=)

add_list_partition_clause ::=

Description of the illustration add_list_partition_clause.gif

(list_values_clause::=, table_partition_description::=, update_index_clauses::=)

coalesce_table_partition::=

Description of the illustration coalesce_table_partition.gif

(update_index_clauses::=, parallel_clause::=)

drop_table_partition::=

Description of the illustration drop_table_partition.gif

`(`update_index_clauses::=, parallel_clause::=)

drop_table_subpartition ::=

Description of the illustration drop_table_subpartition.gif

(update_index_clauses::=, parallel_clause::=)

rename_partition_subpart::=

Description of the illustration rename_partition_subpart.gif

truncate_partition_subpart::=

Description of the illustration truncate_partition_subpart.gif

(update_index_clauses::=, parallel_clause::=)

split_table_partition::=

Description of the illustration split_table_partition.gif

(partition_spec::=, update_index_clauses::=, parallel_clause::=)

split_table_subpartition ::=

Description of the illustration split_table_subpartition.gif

(subpartition_spec::=, update_index_clauses::=, parallel_clause::=)

merge_table_partitions ::=

Description of the illustration merge_table_partitions.gif

(partition_spec::=, update_index_clauses::=, parallel_clause::=)

merge_table_subpartitions ::=

Description of the illustration merge_table_subpartitions.gif

(subpartition_spec::=, parallel_clause::=)

exchange_partition_subpart ::=

Description of the illustration exchange_partition_subpart.gif

(update_index_clauses::=, parallel_clause::=)

exceptions_clause::=

Description of the illustration exceptions_clause.gif

list_values_clause::=

Description of the illustration list_values_clause.gif

range_values_clause::=

Description of the illustration range_values_clause.gif

partitioning_storage_clause::=

Description of the illustration partitioning_storage_clause.gif

partition_attributes::=

Description of the illustration partition_attributes.gif

(physical_attributes_clause::=, logging_clause::=, allocate_extent_clause ::=, deallocate_unused_clause::=, shrink_clause::=, table_compression ::=, modify_LOB_parameters::=)

add_hash_subpartition ::=

Description of the illustration add_hash_subpartition.gif

(subpartition_spec::=, update_index_clauses::=, parallel_clause::=)

add_list_subpartition::=

Description of the illustration add_list_subpartition.gif

(subpartition_spec::=, update_index_clauses::=)

modify_hash_subpartition::=

Description of the illustration modify_hash_subpartition.gif

(allocate_extent_clause ::=, deallocate_unused_clause::=, shrink_clause::=, modify_LOB_parameters::=)

modify_list_subpartition ::=

Description of the illustration modify_list_subpartition.gif

(allocate_extent_clause ::=, deallocate_unused_clause::=, shrink_clause::=, modify_LOB_parameters::=)

table_partition_description::=

Description of the illustration table_partition_description.gif

(segment_attributes_clause::=, table_compression ::=, key_compression::=, LOB_storage_clause::=, varray_col_properties::=)

partition_level_subpartition::=

Description of the illustration partition_level_subpartition.gif

(subpartition_spec::=)

partition_spec::=

Description of the illustration partition_spec.gif

(table_partition_description::=)

subpartition_spec::=

Description of the illustration subpartition_spec.gif

(list_values_clause::=, partitioning_storage_clause::=)

update_index_clauses::=

Description of the illustration update_index_clauses.gif

(update_global_index_clause ::=, update_all_indexes_clause::=)

update_global_index_clause ::=

Description of the illustration update_global_index_clause.gif

update_all_indexes_clause::=

Description of the illustration update_all_indexes_clause.gif

update_index_partition::=

Description of the illustration update_index_partition.gif

update_index_subpartition::=

Description of the illustration update_index_subpartition.gif

index_partition_description::=

Description of the illustration index_partition_description.gif

index_subpartition_clause::=

Description of the illustration index_subpartition_clause.gif

parallel_clause ::=

Description of the illustration parallel_clause.gif

move_table_clause ::=

Description of the illustration move_table_clause.gif

(segment_attributes_clause::=, table_compression ::=, index_org_table_clause ::=, LOB_storage_clause::=, varray_col_properties::=)

enable_disable_clause::=

Description of the illustration enable_disable_clause.gif

(using_index_clause::=, exceptions_clause::=,)

using_index_clause::=

Description of the illustration using_index_clause.gif

(create_index::=, index_properties::=)

index_properties::=

Description of the illustration index_properties.gif

(global_partitioned_index ::=, local_partitioned_index ::=--part of `CREATE` `INDEX`, index_attributes::=, `domain_index_clause`: not supported in `using_index_clause`)

index_attributes::=

Description of the illustration index_attributes.gif

(physical_attributes_clause::=, logging_clause::=, key_compression::=, `parallel_clause`: not supported in `using_index_clause`)

Semantics

Many clauses of the `ALTER` `TABLE` statement have the same functionality they have in a `CREATE` `TABLE` statement. For more information on such clauses, please see CREATE TABLE.

Note: Operations performed by the `ALTER` `TABLE` statement can cause Oracle Database to invalidate procedures and stored functions that access the table. For information on how and when the database invalidates such objects, see Oracle Database Concepts.

schema

Specify the schema containing the table. If you omit `schema`, then Oracle Database assumes the table is in your own schema.

table

Specify the name of the table to be altered.

Note: If you alter a table that is a master table for one or more materialized views, then Oracle Database marks the materialized views `INVALID`. Invalid materialized views cannot be used by query rewrite and cannot be refreshed. For information on revalidating a materialized view, see ALTER MATERIALIZED VIEW.

See Also: Oracle Data Warehousing Guide for more information on materialized views in general

Restrictions on Altering Temporary Tables You can modify, drop columns from, or rename a temporary table. However, for a temporary table you cannot:

- Add columns of nested table type. You can add columns of other types.

- Specify referential integrity (foreign key) constraints for an added or modified column.

- Specify the following clauses of the `LOB_storage_clause` for an added or modified LOB column: `TABLESPACE`, `storage_clause`, `logging_clause`, `allocate_extent_clause`, or `deallocate_unused_clause`.

- Specify the `physical_attributes_clause`, `nested_table_col_properties`, `parallel_clause`, `allocate_extent_clause`, `deallocate_unused_clause`, or any of the index-organized table clauses.

- Exchange partitions between a partition and a temporary table.

- Specify the `logging_clause`.

- Specify `MOVE.`

Restrictions on Altering External Tables You can add, drop, or modify the columns of an external table. However, for an external table you cannot:

- Add a `LONG`, LOB, or object type column or change the datatype of an external table column to any of these datatypes.

- Add a constraint to an external table.

- Modify the storage parameters of an external table.

- Specify the `logging_clause`.

- Specify `MOVE`.

alter_table_properties

Use the `alter_table_clauses` to modify a database table.

physical_attributes_clau se

The `physical_attributes_clause` lets you change the value of the `PCTFREE`, `PCTUSED`, and `INITRANS` parameters and storage characteristics. Please refer to physical_attributes_clause and storage_clause for a full description of these parameters and characteristics.

Restrictions on Altering Table Physical Attributes Altering physical attributes is subject to the following restrictions:

- You cannot specify the `PCTUSED` parameter for the index segment of an index-organized table.

- If you attempt to alter the storage attributes of tables in locally managed tablespaces, then Oracle Database raises an error. However, if some segments of a partitioned table reside in a locally managed tablespace and other segments reside in a dictionary-managed tablespace, then the database alters the storage attributes of the segments in the dictionary-managed tablespace but does not alter the attributes of the segments in the locally managed tablespace, and does not raise an error.

- For segments with automatic segment-space management, the database ignores attempts to change the `PCTUSED` setting. If you alter the `PCTFREE` setting, then you must subsequently run the `DBMS_REPAIR.SEGMENT_FIX_STATUS` procedure to implement the new setting on blocks already allocated to the segment.

Cautions on Altering Tables Physical Attributes The values you specify in this clause affect the table as follows:

- For a nonpartitioned table, the values you specify override any values specified for the table at create time.

- For a range-, list-, or hash-partitioned table, the values you specify are the default values for the table and the actual values for every existing partition, overriding any values already set for the partitions. To change default table attributes without overriding existing partition values, use the `modify_table_default_attrs` clause.

- For a composite-partitioned table, the values you specify are the default values for the table and all partitions of the table and the actual values for all subpartitions of the table, overriding any values already set for the subpartitions. To change default partition attributes without overriding existing subpartition values, use the `modify_table_default_attrs` clause with the `FOR` `PARTITION` clause.

logging_clause

Use the `logging_clause` to change the logging attribute of the table. The `logging_clause` specifies whether subsequent `ALTER` `TABLE` ... `MOVE` and `ALTER` `TABLE` ... `SPLIT` operations will be logged or not logged.

When used with the `modify_table_default_attrs` clause, this clause affects the logging attribute of a partitioned table.

See Also: logging_clause for a full description of this clause Oracle Data Warehousing Guide for more information about the `logging_clause` and parallel DML

table_compression

The `table_compression` clause is valid only for heap-organized tables. Use this clause to instruct Oracle Database whether to compress data segments to reduce disk and memory use. The `COMPRESS` keyword enables table compression. The `NOCOMPRESS` keyword disables table compression.

Note: The first time a table is altered in such a way that compressed data will be added, all bitmap indexes and bitmap index partitions on that table must be marked `UNUSABLE`.

See Also: Oracle Database Performance Tuning Guide for information on calculating the compression ratio and to Oracle Data Warehousing Guide for information on table compression usage scenarios table_compression clause of `CREATE` `TABLE` information on creating objects with table compression

supplemental_table_logging

Use the `supplemental_table_logging` clause to add or drop a redo log group or one or more supplementally logged columns in a redo log group.

- In the `ADD` clause, use `supplemental_log_grp_clause` to create named supplemental log group. Use the `supplemental_id_key_clause` to create a system-generated log group.

- On the `DROP` clause, use `GROUP` `log_group` syntax to drop a named supplemental log group and use the `supplemental_id_key_clause` to drop a system-generated log group.

The `supplemental_log_grp_clause` and the `supplemental_id_key_clause` have the same semantics in `CREATE` `TABLE` and `ALTER` `TABLE` statements. For full information on these clauses, please refer to supplemental_log_grp_clause and supplemental_id_key_clause in the documentation on `CREATE` `TABLE`.

See Also: Oracle Data Guard Concepts and Administration for information on supplemental redo log groups

allocate_extent_clause

Use the `allocate_extent_clause` to explicitly allocate a new extent for the table, the partition or subpartition, the overflow data segment, the LOB data segment, or the LOB index.

Restriction on Allocating Table Extents You cannot allocate an extent for a temporary table or for a range- or composite-partitioned table.

See Also: allocate_extent_clause for a full description of this clause and "Allocating Extents: Example"

deallocate_unused_clause

Use the `deallocate_unused_clause` to explicitly deallocate unused space at the end of the table, partition or subpartition, overflow data segment, LOB data segment, or LOB index and make the space available for other segments in the tablespace.

See Also: deallocate_unused_clause for a full description of this clause and "Deallocating Unused Space: Example"

shrink_clause

The shrink clause lets you manually shrink space in a table, index-organized table or its overflow segment, index, partition, subpartition, LOB segment, materialized view, or materialized view log. This clause is valid only for segments in tablespaces with automatic segment management. By default, Oracle Database compacts the segment, adjusts the high water mark, and releases the recuperated space immediately.

Compacting the segment requires row movement. Therefore, you must enable row movement for the object you want to shrink before specifying this clause. Further, if your application has any rowid-based triggers, you should disable them before issuing this clause.

COMPACT If you specify `COMPACT`, then Oracle Database only defragments the segment space and compacts the table rows for subsequent release. The database does not readjust the high water mark and does not release the space immediately. You must issue another `ALTER` `TABLE` ... `SHRINK` `SPACE` statement later to complete the operation. This clause is useful if you want to accomplish the shrink operation in two shorter steps rather than one longer step.

For an index or index-organized table, specifying `ALTER` [`INDEX` | `TABLE`] ... `SHRINK` `SPACE` `COMPACT` is equivalent to specifying `ALTER` [`INDEX` | `TABLE` ... `COALESCE`. The `shrink_clause` can be cascaded (please refer to the `CASCADE` clause, which follows) and compacts the segment more densely than does a coalesce operation, which can improve performance. However, if you do not want to release the unused space, then you can use the appropriate `COALESCE` clause.

CASCADE If you specify `CASCADE`, then Oracle Database performs the same operations on all dependent objects of `table`, including secondary indexes on index-organized tables.

Restrictions on the shrink_clause The `shrink_clause` is subject to the following restrictions:

- You cannot specify this clause for a cluster, a clustered table, or any object with a `LONG` column.

- Segment shrink is not supported for tables with function-based indexes or bitmap join indexes.

- This clause does not shrink mapping tables of index-organized tables, even if you specify `CASCADE`.

- You cannot specify this clause for a compressed table.

- You cannot shrink a table that is the master table of an `ON` `COMMIT` materialized view. Rowid materialized views must be rebuilt after the shrink operation.

CACHE | NOCACHE

The `CACHE` and `NOCACHE` clauses have the same semantics in `CREATE` `TABLE` and `ALTER` `TABLE` statements. For complete information on these clauses, please refer to "CACHE | NOCACHE | CACHE READS" in the documentation on `CREATE` `TABLE`. If you omit both of these clauses in an `ALTER` `TABLE` statement, then the existing value is unchanged.

upgrade_table_clause

The `upgrade_table_clause` is relevant for object tables and for relational tables with object columns. It lets you instruct Oracle Database to convert the metadata of the target table to conform with the latest version of each referenced type. If table is already valid, then the table metadata remains unchanged.

Restriction on Upgrading Object Tables and Columns Within this clause, you cannot specify `object_type_col_properties` as a clause of `column_properties`.

INCLUDING DATA Specify `INCLUDING` `DATA` if you want Oracle Database to convert the data in the table to the latest type version format. You can define the storage for any new column while upgrading the table by using the column_properties and the LOB_partition_storage . This is the default.

You can convert data in the table at the time you upgrade the type by specifying `CASCADE` `INCLUDING` `TABLE` `DATA` in the `dependent_handling_clause` of the `ALTER` `TYPE` statement. See dependent_handling_clause . For information on whether a table contains data based on an older type version, refer to the `DATA_UPGRADED` column of the `USER_TAB_COLUMNS` data dictionary view.

NOT INCLUDING DATA Specify `NOT` `INCLUDING` `DATA` if you want Oracle Database to leave column data unchanged.

Restriction on NOT INCLUDING DATA You cannot specify `NOT` `INCLUDING` `DATA` if the table contains columns in Oracle8 release 8.0.x image format. To determine whether the table contains such columns, refer to the `V80_FMT_IMAGE` column of the `USER_TAB_COLUMNS` data dictionary view.

See Also: Oracle Database Reference for information on the data dictionary views ALTER TYPE for information on converting dependent table data when modifying a type upon which the table depends Oracle Database Application Developer's Guide - Object-Relational Features for more information on the implications of not converting table data to the latest type version format

records_per_block_clause

The `records_per_block_clause` lets you specify whether Oracle Database restricts the number of records that can be stored in a block. This clause ensures that any bitmap indexes subsequently created on the table will be as compressed as possible.

Restrictions on Records in a Block The `record_per_block_clause` is subject to the following restrictions:

- You cannot specify either `MINIMIZE` or `NOMINIMIZE` if a bitmap index has already been defined on table. You must first drop the bitmap index.

- You cannot specify this clause for an index-organized table or a nested table.

MINIMIZE Specify `MINIMIZE` to instruct Oracle Database to calculate the largest number of records in any block in the table and to limit future inserts so that no block can contain more than that number of records.

Oracle recommends that a representative set of data already exist in the table before you specify `MINIMIZE`. If you are using table compression (see table_compression ), then a representative set of compressed data should already exist in the table.

Restriction on MINIMIZE You cannot specify `MINIMIZE` for an empty table.

NOMINIMIZE Specify `NOMINIMIZE` to disable the `MINIMIZE` feature. This is the default.

row_movement_clause

This clause has the same semantics in `CREATE` `TABLE` and `ALTER` `TABLE` statements. For complete information on these clauses, please refer to row_movement_clause in the documentation on `CREATE` `TABLE`.

RENAME TO

Use the `RENAME` clause to rename `table` to `new_table_name`.

Note: Using this clause invalidates any dependent materialized views. For more information on materialized views, see CREATE MATERIALIZED VIEW and Oracle Data Warehousing Guide.

alter_iot_clauses

index_org_table_clause

This clause lets you change a table that is not index organized into an index-organized table. Index-organized tables keep data sorted on the primary key and are therefore best suited for primary-key-based access and manipulation.See index_org_table_clause in the context of `CREATE` `TABLE`.

See Also: "Modifying Index-Organized Tables: Examples"

key_compression

This clause is relevant only if `table` is index organized. Specify `COMPRESS` to instruct Oracle Database to combine the primary key index blocks of the index-organized table where possible to free blocks for reuse. You can specify this clause with the `parallel_clause`.

PCTTHRESHOLD integer Please refer to "PCTTHRESHOLD integer" in the documentation of `CREATE` `TABLE`.

INCLUDING column_name Please refer to "INCLUDING column_name" in the documentation of `CREATE` `TABLE`.

overflow_attributes

The `overflow_attributes` let you specify the overflow data segment physical storage and logging attributes to be modified for the index-organized table. Parameter values specified in this clause apply only to the overflow data segment.

See Also: CREATE TABLE

add_overflow_clause

The `add_overflow_clause` lets you add an overflow data segment to the specified index-organized table. You can also use this clause to explicitly allocate an extent to or deallocate unused space from an existing overflow segment.

Use the `STORE` `IN` `tablespace` clause to specify tablespace storage for the entire overflow segment. Use the `PARTITION` clause to specify tablespace storage for the segment by partition.

For a partitioned index-organized table:

- If you do not specify `PARTITION`, then Oracle Database automatically allocates an overflow segment for each partition. The physical attributes of these segments are inherited from the table level.

- If you want to specify separate physical attributes for one or more partitions, then you must specify such attributes for every partition in the table. You need not specify the name of the partitions, but you must specify their attributes in the order in which they were created.

You can find the order of the partitions by querying the `PARTITION_NAME` and `PARTITION_POSITION` columns of the `USER_IND_PARTITIONS` view.

If you do not specify `TABLESPACE` for a particular partition, then the database uses the tablespace specified for the table. If you do not specify `TABLESPACE` at the table level, then the database uses the tablespace of the partition primary key index segment.

Restrictions on Overflow Attributes Within the `segment_attributes_clause`:

- You cannot specify the `OPTIMAL` parameter of the `physical_attributes_clause`.

- You cannot specify tablespace storage for the overflow segment using this clause. For a nonpartitioned table, you can use `ALTER` `TABLE` ... `MOVE` ... `OVERFLOW` to move the segment to a different tablespace. For a partitioned table, use `ALTER` `TABLE` ... `MODIFY` `DEFAULT` `ATTRIBUTES` ... `OVERFLOW` to change the default tablespace of the overflow segment.

Additional restrictions apply if `table` is in a locally managed tablespace, because in such tablespaces several segment attributes are managed automatically by the database.

See Also: allocate_extent_clause and deallocate_unused_clause for full descriptions of these clauses of the `add_overflow_clause`

alter_overflow_clause

The `alter_overflow_clause` lets you change the definition of the overflow segment of an existing index-organized table.

The restrictions that apply to the `add_overflow_clause` also apply to the `alter_overflow_clause`.

Note: When you add a column to an index-organized table, Oracle Database evaluates the maximum size of each column to estimate the largest possible row. If an overflow segment is needed but you have not specified `OVERFLOW`, then the database raises an error and does not execute the `ALTER` `TABLE` statement. This checking function guarantees that subsequent DML operations on the index-organized table will not fail because an overflow segment is lacking.

alter_mapping_table_clauses

The `alter_mapping_table_clauses` is valid only if `table` is index organized and has a mapping table.

allocate_extent_clause Use the `allocate_extent_clause` to allocate a new extent at the end of the mapping table for the index-organized table. Please refer to allocate_extent_clause for a full description of this clause.

deallocate_unused_clause Specify the `deallocate_unused_clause` to deallocate unused space at the end of the mapping table of the index-organized table. Please refer to deallocate_unused_clause for a full description of this clause.

Oracle Database automatically maintains all other attributes of the mapping table or its partitions.

COALESCE Clause

Specify `COALESCE` to instruct Oracle Database to merge the contents of index blocks of the index the database uses to maintain the index-organized table where possible to free blocks for reuse. Please refer to the shrink_clause for information on the relationship between these two clauses.

column_clauses

Use these clauses to add, drop, or otherwise modify a column.

add_column_clause

The `add_column_clause` lets you add a column to a table.

See Also: CREATE TABLE for a description of the keywords and parameters of this clause and "Adding a Table Column: Example"

column_definition

Unless otherwise noted in this section, the elements of `column_definition` have the same behavior when adding a column to an existing table as they do when creating a new table. Please refer to column_definition for information.

Restriction on column_definition The `SORT` parameter is valid only when creating a new table. You cannot specify `SORT` in the column_definition of an `ALTER` `TABLE` ... `ADD` statement.

If you add a column, then the initial value of each row for the new column is null unless you specify the `DEFAULT` clause. In this case, Oracle Database updates each row in the new column with the value you specify for `DEFAULT`. This update operation, in turn, fires any `AFTER` `UPDATE` triggers defined on the table.

Note: If a column has a default value, then you can use the `DEFAULT` clause to change the default to `NULL`, but you cannot remove the default value completely. That is, if a column has ever had a default value assigned to it, then the `DATA_DEFAULT` column of the `USER_TAB_COLUMNS` data dictionary view will always display either a default value or `NULL`.

You can add an overflow data segment to each partition of a partitioned index-organized table.

You can add LOB columns to nonpartitioned and partitioned tables. You can specify LOB storage at the table and at the partition or subpartition level.

If you previously created a view with a query that used the `SELECT` `*` syntax to select all columns from table, and you now add a column to `table`, then the database does not automatically add the new column to the view. To add the new column to the view, re-create the view using the `CREATE` `VIEW` statement with the `OR` `REPLACE` clause. Please refer to CREATE VIEW for more information.

Restrictions on Adding Columns The addition of columns is subject to the following restrictions:

- You cannot add a LOB column to a cluster table.

- If you add a LOB column to a hash-partitioned table, then the only attribute you can specify for the new partition is `TABLESPACE`.

- You cannot add a column with a `NOT` `NULL` constraint if `table` has any rows unless you also specify the `DEFAULT` clause.

- If you specify this clause for an index-organized table, then you cannot specify any other clauses in the same statement.

- You cannot add a column with a default value to a compressed table or to a partitioned table containing any compressed partition, unless you first disable compression for the table or partition.

DEFAULT

Use the `DEFAULT` clause to specify a default for a new column or a new default for an existing column. Oracle Database assigns this value to the column if a subsequent `INSERT` statement omits a value for the column. If you are adding a new column to the table and specify the default value, then the database inserts the default column value into all rows of the table.

The datatype of the default value must match the datatype specified for the column. The column must also be large enough to hold the default value.

Restrictions on Default Column Values Default column values are subject to the following restrictions:

- A `DEFAULT` expression cannot contain references to other columns, the pseudocolumns `CURRVAL`, `NEXTVAL`, `LEVEL`, and `ROWNUM`, or date constants that are not fully specified.

- The expression can be of any form except a scalar subquery expression.

See Also: "Specifying Default Column Value: Examples"

inline_constraint

Use `inline_constraint` to add a constraint to the new column.

inline_ref_constraint

This clause lets you describe a new column of type `REF`. Please refer to constraint for syntax and description of this type of constraint, including restrictions.

column_properties

The clauses of `column_properties` determine the storage characteristics of an object type, nested table, varray, or LOB column.

object_type_col_properties This clause is valid only when you are adding a new object type column or attribute. To modify the properties of an existing object type column, use the `modify_column_clauses`. The semantics of this clause are the same as for `CREATE` `TABLE` unless otherwise noted.

Use the `object_type_col_properties` clause to specify storage characteristics for a new object column or attribute or an element of a collection column or attribute.

For complete information on this clause, please refer to object_type_col_properties in the documentation on `CREATE` `TABLE`.

nested_table_col_properties The `nested_table_col_properties` clause lets you specify separate storage characteristics for a nested table, which in turn lets you to define the nested table as an index-organized table. You must include this clause when creating a table with columns or column attributes whose type is a nested table. (Clauses within this clause that function the same way they function for parent object tables are not repeated here.)

- For `nested_item`, specify the name of a column (or a top-level attribute of the nested table object type) whose type is a nested table.

  If the nested table is a multilevel collection, and the inner nested table does not have a name, then specify `COLUMN_VALUE` in place of the `nested_item` name.

- For `storage_table`, specify the name of the table where the rows of `nested_item` reside. The storage table is created in the same schema and the same tablespace as the parent table.

Restrictions on Nested Table Column Properties Nested table column properties are subject to the following restrictions:

- You cannot specify the `parallel_clause`.

- You cannot specify `CLUSTER` as part of the `physical_properties` clause.

See Also: "Nested Tables: Examples"

varray_col_properties The `varray_col_properties` clause lets you specify separate storage characteristics for the LOB in which a varray will be stored. If you specify this clause, then Oracle Database will always store the varray in a LOB, even if it is small enough to be stored inline. If `varray_item` is a multilevel collection, then the database stores all collection items nested within `varray_item` in the same LOB in which `varray_item` is stored.

Restriction on Varray Column Properties You cannot specify `TABLESPACE` as part of `LOB_parameters` for a varray column. The LOB tablespace for a varray defaults to the tablespace of the containing table.

LOB_storage_clause Use the `LOB_storage_clause` to specify the LOB storage characteristics for a newly added LOB column, partition, or subpartition, or when you are converting a `LONG` column into a LOB column. You cannot use this clause to modify an existing LOB. Instead, you must use the `modify_LOB_storage_clause`.

CACHE READS Clause `CACHE` `READS` applies only to LOB storage. It indicates that LOB values are brought into the buffer only during read operations but not during write operations.

- For `LOB_item`, specify the LOB column name or LOB object attribute for which you are explicitly defining tablespace and storage characteristics that are different from those of the table.

- For `LOB_segname`, specify the name of the LOB data segment. You cannot use `LOB_segname` if more than one `LOB_item` is specified.

When you add a new LOB column, you can specify the logging attribute with `CACHE` `READS`, as you can when defining a LOB column at create time.

When you modify a LOB column from `CACHE` or `NOCACHE` to `CACHE` `READS,` or from `CACHE` `READS` to `CACHE` or `NOCACHE`, you can change the logging attribute. If you do not specify `LOGGING` or `NOLOGGING`, then this attribute defaults to the current logging attribute of the LOB column.

For existing LOBs, if you do not specify `CACHE`, `NOCACHE`, or `CACHE` `READS`, then Oracle Database retains the existing values of the LOB attributes.

Restriction on LOB Parameters The only parameter of `LOB_parameters` you can specify for a hash partition or hash subpartition is `TABLESPACE`.

ENABLE | DISABLE STORAGE IN ROW Specify whether the LOB value is to be stored in the row (inline) or outside of the row (out of line). The LOB locator is always stored inline regardless of where the LOB value is stored.

- `ENABLE` specifies that the LOB value is stored inline if its length is less than approximately 4000 bytes minus system control information. This is the default.

- `DISABLE` specifies that the LOB value is stored out of line regardless of the length of the LOB value.

Restrictions on Enabling Storage in Row You cannot change `STORAGE` `IN` `ROW` once it is set. Therefore, you cannot specify this clause as part of the `modify_col_properties` clause. However, you can change this setting when adding a new column (add_column_clause ) or when moving the table (move_table_clause ).

CHUNK integer Specify the number of bytes to be allocated for LOB manipulation. If `integer` is not a multiple of the database block size, then Oracle Database rounds up (in bytes) to the next multiple. For example, if the database block size is 2048 and `integer` is 2050, then the database allocates 4096 bytes (2 blocks).The maximum value is 32768 (32 K), which is the largest Oracle Database block size allowed. The default `CHUNK` size is one Oracle Database block.

Restrictions on CHUNK The `CHUNK` parameter is subject to the following restrictions:

- You cannot change the value of `CHUNK` once it is set.

- The value of `CHUNK` must be less than or equal to the value of `NEXT` (either the default value or that specified in the storage clause). If `CHUNK` exceeds the value of `NEXT`, then Oracle Database returns an error.

PCTVERSION integer Specify the maximum percentage of overall LOB storage space to be used for maintaining old versions of the LOB. The default value is 10, meaning that older versions of the LOB data are not overwritten until 10% of the overall LOB storage space is used.

RETENTION If the database is in automatic undo mode, then you can specify `RETENTION` instead of `PCTVERSION` to instruct Oracle Database to retain old versions of this LOB. This clause overrides any prior setting of `PCTVERSION`.

Restriction on RETENTION You cannot specify `RETENTION` if the database is running in manual undo mode. Please refer to LOB_parameters for a full description of the `RETENTION` parameter.

FREEPOOLS integer If the database is in automatic undo mode, then you can use this clause to specify the number of freelist groups for this LOB. This clause overrides any prior setting of `FREELIST` `GROUPS`.

Restriction on FREEPOOLS You cannot specify `FREEPOOLS` if the database is running in manual undo mode. Please refer to LOB_parameters for a full description of the `FREEPOOLS` parameter.

LOB_partition_storage

The `LOB_partition_storage` clause lets you specify a separate `LOB_storage_clause` or `varray_col_properties` clause for each partition. You must specify the partitions in the order of partition position. You can find the order of the partitions by querying the `PARTITION_NAME` and `PARTITION_POSITION` columns of the `USER_IND_PARTITIONS` view.

If you do not specify a `LOB_storage_clause` or `varray_col_properties` clause for a particular partition, then the storage characteristics are those specified for the LOB item at the table level. If you also did not specify any storage characteristics for the LOB item at the table level, then Oracle Database stores the LOB data partition in the same tablespace as the table partition to which it corresponds.

Restriction on LOB Partition Storage You can specify only one list of `LOB_partition_storage` clauses in a single `ALTER` `TABLE` statement, and all `LOB_storage_clauses` and `varray_col_properties` clause must precede the list of `LOB_partition_storage` clauses.

XMLType_column_properties Please refer to the complete description of this clause in XMLType_column_properties in the documentation on `CREATE` `TABLE`.

XMLSchema_spec Please refer to the complete description of this clause in XMLSchema_spec in the documentation on `CREATE` `TABLE`.

See Also: LOB_storage_clause for information on the `LOB_segname` and `LOB_parameters` clauses "XMLType Column Examples" for an example of `XMLType` columns in object-relational tables and "Using XML in SQL Statements" for an example of creating an XMLSchema Oracle XML DB Developer's Guide for more information on `XMLType` columns and tables and on creating an XMLSchema

modify_column_clauses

Use the `modify_column_clauses` to modify the properties of an existing column or the substitutability of an existing object type column.

See Also: "Modifying Table Columns: Examples"

modify_col_properties

Use this clause to modify the properties of the column. Any of the optional parts of the column definition (datatype, default value, or constraint) that you omit from this clause remain unchanged.

datatype You can change the datatype of any column if all rows of the column contain nulls. However, if you change the datatype of a column in a materialized view container table, then Oracle Database invalidates the corresponding materialized view.

You can omit the datatype only if the statement also designates the column as part of the foreign key of a referential integrity constraint. The database automatically assigns the column the same datatype as the corresponding column of the referenced key of the referential integrity constraint.

You can always increase the size of a character or raw column or the precision of a numeric column, whether or not all the rows contain nulls. You can reduce the size of a datatype of a column as long as the change does not require data to be modified.The database scans existing data and returns an error if data exists that exceeds the new length limit.

You can modify a `DATE` column to `TIMESTAMP` or `TIMESTAMP` `WITH` `LOCAL` `TIME` `ZONE`. You can modify any `TIMESTAMP` `WITH` `LOCAL` `TIME` `ZONE` to a `DATE` column.

Note: When you modify a `TIMESTAMP` `WITH` `LOCAL` `TIME` `ZONE` column to a `DATE` column, the fractional seconds and time zone adjustment data is lost. If the `TIMESTAMP` `WITH` `LOCAL` `TIME` `ZONE` data has fractional seconds, then Oracle Database updates the row data for the column by rounding the fractional seconds. If the `TIMESTAMP` `WITH` `LOCAL` `TIME` `ZONE` data has the minute field greater than or equal to 60 (which can occur in a boundary case when the daylight savings rule switches), then Oracle Database updates the row data for the column by subtracting 60 from its minute field.

If the table is empty, then you can increase or decrease the leading field or the fractional second value of a datetime or interval column. If the table is not empty, then you can only increase the leading field or fractional second of a datetime or interval column.

You can change a `LONG` column to a `CLOB` or `NCLOB` column, and a `LONG` `RAW` column to a `BLOB` column.

- The modified LOB column inherits all constraints and triggers that were defined on the original `LONG` column. If you want to change any constraints, then you must do so in a subsequent `ALTER` `TABLE` statement.

- If any domain indexes are defined on the `LONG` column, then you must drop them before modifying the column to a LOB.

- After the modification, you will have to rebuild all other indexes on all columns of the table.

  See Also: Oracle Database Application Developer's Guide - Fundamentals for information on `LONG` and LOB columns Oracle Database Application Developer's Guide - Large Objects for information on `LONG` to LOB migration ALTER INDEX for information on dropping and rebuilding indexes

For `CHAR` and `VARCHAR2` columns, you can change the length semantics by specifying `CHAR` (to indicate character semantics for a column that was originally specified in bytes) or `BYTE` (to indicate byte semantics for a column that was originally specified in characters). To learn the length semantics of existing columns, query the `CHAR_USED` column of the `ALL_`, `USER_`, or `DBA_TAB_COLUMNS` data dictionary view.

See Also: Oracle Database Globalization Support Guide for information on byte and character semantics Oracle Database Reference for information on the data dictionary views

ENCRYPT encryption_spec | DECRYPT Use this clause to decrypt an encrypted column, to encrypt an unencrypted column, or to change the `SALT` option of an encrypted column.

When encrypting an existing column, if you specify `encryption_spec`, it must match the encryption specification of any other encrypted columns in the same table. Please refer to the `CREATE` `TABLE` clause ENCRYPT encryption_spec for additional information and restrictions on the `encryption_spec`.

See Also: "Data Encryption: Examples"

inline_constraint This clause lets you add a constraint to a column you are modifying. To change the state of existing constraints on existing columns, use the `constraint_clauses`.

LOB_storage_clause The `LOB_storage_clause` is permitted within `modify_col_properties` only if you are converting a `LONG` column to a LOB column. In this case only, you can specify LOB storage for the column using the `LOB_storage_clause`. However, you can specify only the single column as a `LOB_item`. Default LOB storage attributes are used for any attributes you omit in the `LOB_storage_clause`.

Restrictions on Modifying Column Properties The modification of column properties is subject to the following restrictions:

- You cannot change the datatype of a LOB column.

- You cannot modify a column of a table if a domain index is defined on the column. You must first drop the domain index and then modify the column.

- You cannot modify the datatype or length of a column that is part of the partitioning or subpartitioning key of a table or index.

- You can change a `CHAR` column to `VARCHAR2` (or `VARCHAR`) and a `VARCHAR2` (or `VARCHAR`) column to `CHAR` only if the `BLANK_TRIMMING` initialization parameter is set to `TRUE` and the column size stays the same or increases. If the `BLANK_TRIMMING` initialization parameter is set to `TRUE`, then you can also reduce the column size to any size greater than or equal to the maximum trimmed data value.

- You cannot change a `LONG` or `LONG` `RAW` column to a LOB if the table is part of a cluster. If you do change a `LONG` or `LONG` `RAW` column to a LOB, then the only other clauses you can specify in this `ALTER` `TABLE` statement are the `DEFAULT` clause and the `LOB_storage_clause`.

- You can specify the `LOB_storage_clause` as part of `modify_col_properties` only when you are changing a `LONG` or `LONG` `RAW` column to a LOB.

- You cannot specify a column of datatype `ROWID` for an index-organized table, but you can specify a column of type `UROWID`.

- You cannot change the datatype of a column to `REF`.

See Also: ALTER MATERIALIZED VIEW for information on revalidating a materialized view

modify_col_substitutable

Use this clause to set or change the substitutability of an existing object type column.

The `FORCE` keyword drops any hidden columns containing typeid information or data for subtype attributes. You must specify `FORCE` if the column or any attributes of its type are not `FINAL`.

Restrictions on Modifying Column Substitutability The modification of column substitutability is subject to the following restrictions:

- You can specify this clause only once in any `ALTER` `TABLE` statement.

- You cannot modify the substitutability of a column in an object table if the substitutability of the table itself has been set.

- You cannot specify this clause if the column was created or added using the `IS` `OF` `TYPE` syntax, which limits the range of subtypes permitted in an object column or attribute to a particular subtype. Please refer to substitutable_column_clause in the documentation on `CREATE` `TABLE` for information on the `IS` `OF` `TYPE` syntax.

- You cannot change a varray column to `NOT` `SUBSTITUTABLE`, even by specifying `FORCE`, if any of its attributes are nested object types that are not `FINAL`.

drop_column_clause

The `drop_column_clause` lets you free space in the database by dropping columns you no longer need or by marking them to be dropped at a future time when the demand on system resources is less.

- If you drop a nested table column, then its storage table is removed.

- If you drop a LOB column, then the LOB data and its corresponding LOB index segment are removed.

- If you drop a `BFILE` column, then only the locators stored in that column are removed, not the files referenced by the locators.

- If you drop or mark unused a column defined as an `INCLUDING` column, then the column stored immediately before this column will become the new `INCLUDING` column.

SET UNUSED Clause

Specify `SET` `UNUSED` to mark one or more columns as unused. Specifying this clause does not actually remove the target columns from each row in the table. That is, it does not restore the disk space used by these columns. Therefore, the response time is faster than when you execute the `DROP` clause.

You can view all tables with columns marked `UNUSED` in the data dictionary views `USER_UNUSED_COL_TABS`, `DBA_UNUSED_COL_TABS`, and `ALL_UNUSED_COL_TABS`.

See Also: Oracle Database Reference for information on the data dictionary views

Unused columns are treated as if they were dropped, even though their column data remains in the table rows. After a column has been marked `UNUSED`, you have no access to that column. A `SELECT` `*` query will not retrieve data from unused columns. In addition, the names and types of columns marked `UNUSED` will not be displayed during a `DESCRIBE`, and you can add to the table a new column with the same name as an unused column.

Note: Until you actually drop these columns, they continue to count toward the absolute limit of 1000 columns in a single table. However, as with all DDL statements, you cannot roll back the results of this clause. That is, you cannot issue `SET` `USED` counterpart to retrieve a column that you have `SET` `UNUSED`. Please refer to CREATE TABLE for more information on the 1000-column limit. Also, if you mark a `LONG` column as `UNUSED`, then you cannot add another `LONG` column to the table until you actually drop the unused `LONG` column.

DROP Clause

Specify `DROP` to remove the column descriptor and the data associated with the target column from each row in the table. If you explicitly drop a particular column, then all columns currently marked `UNUSED` in the target table are dropped at the same time.

When the column data is dropped:

- All indexes defined on any of the target columns are also dropped.

- All constraints that reference a target column are removed.

- If any statistics types are associated with the target columns, then Oracle Database disassociates the statistics from the column with the `FORCE` option and drops any statistics collected using the statistics type.

Note: If the target column is a parent key of a nontarget column, or if a check constraint references both the target and nontarget columns, then Oracle Database returns an error and does not drop the column unless you have specified the `CASCADE` `CONSTRAINTS` clause. If you have specified that clause, then the database removes all constraints that reference any of the target columns.

See Also: DISASSOCIATE STATISTICS for more information on disassociating statistics types

DROP UNUSED COLUMNS Clause

Specify `DROP` `UNUSED` `COLUMNS` to remove from the table all columns currently marked as unused. Use this statement when you want to reclaim the extra disk space from unused columns in the table. If the table contains no unused columns, then the statement returns with no errors.

column Specify one or more columns to be set as unused or dropped. Use the `COLUMN` keyword only if you are specifying only one column. If you specify a column list, then it cannot contain duplicates.

CASCADE CONSTRAINTS Specify `CASCADE` `CONSTRAINTS` if you want to drop all foreign key constraints that refer to the primary and unique keys defined on the dropped columns as well as all multicolumn constraints defined on the dropped columns. If any constraint is referenced by columns from other tables or remaining columns in the target table, then you must specify `CASCADE` `CONSTRAINTS`. Otherwise, the statement aborts and an error is returned.

INVALIDATE The `INVALIDATE` keyword is optional. Oracle Database automatically invalidates all dependent objects, such as views, triggers, and stored program units. Object invalidation is a recursive process. Therefore, all directly dependent and indirectly dependent objects are invalidated. However, only local dependencies are invalidated, because the database manages remote dependencies differently from local dependencies.

An object invalidated by this statement is automatically revalidated when next referenced. You must then correct any errors that exist in that object before referencing it.

See Also: Oracle Database Concepts for more information on dependencies

CHECKPOINT Specify `CHECKPOINT` if you want Oracle Database to apply a checkpoint for the `DROP` `COLUMN` operation after processing `integer` rows; `integer` is optional and must be greater than zero. If `integer` is greater than the number of rows in the table, then the database applies a checkpoint after all the rows have been processed. If you do not specify `integer`, then the database sets the default of 512. Checkpointing cuts down the amount of undo logs accumulated during the `DROP` `COLUMN` operation to avoid running out of undo space. However, if this statement is interrupted after a checkpoint has been applied, then the table remains in an unusable state. While the table is unusable, the only operations allowed on it are `DROP` `TABLE`, `TRUNCATE` `TABLE`, and `ALTER` `TABLE` `DROP` ... `COLUMNS` `CONTINUE` (described in sections that follow).

You cannot use this clause with `SET` `UNUSED`, because that clause does not remove column data.

DROP COLUMNS CONTINUE Clause

Specify `DROP` `COLUMNS` `CONTINUE` to continue the drop column operation from the point at which it was interrupted. Submitting this statement while the table is in an invalid state results in an error.

Restrictions on Dropping Columns Dropping columns is subject to the following restrictions:

- Each of the parts of this clause can be specified only once in the statement and cannot be mixed with any other `ALTER` `TABLE` clauses. For example, the following statements are not allowed:

      ALTER TABLE t1 DROP COLUMN f1 DROP (f2);
      ALTER TABLE t1 DROP COLUMN f1 SET UNUSED (f2);
      ALTER TABLE t1 DROP (f1) ADD (f2 NUMBER);
      ALTER TABLE t1 SET UNUSED (f3)
         ADD (CONSTRAINT ck1 CHECK (f2 > 0));

- You can drop an object type column only as an entity. To drop an attribute from an object type column, use the `ALTER` `TYPE` ... `DROP` `ATTRIBUTE` statement with the `CASCADE` `INCLUDING` `TABLE` `DATA` clause. Be aware that dropping an attribute affects all dependent objects. See DROP ATTRIBUTE for more information.

- You cannot specify the `DROP` clause to drop a column from a compressed table or a partitioned table containing any compressed partition, unless you first disable data compression for the table or partition. You can only use the `SET` `UNUSED` clause

- You can drop a column from an index-organized table only if it is not a primary key column. The primary key constraint of an index-organized table can never be dropped, so you cannot drop a primary key column even if you have specified `CASCADE` `CONSTRAINTS`.

- You can export tables with dropped or unused columns. However, you can import a table only if all the columns specified in the export files are present in the table (that is, none of those columns has been dropped or marked unused). Otherwise, Oracle Database returns an error.

- You cannot drop a column on which a domain index has been built.

- You cannot drop a `SCOPE` table constraint or a `WITH` `ROWID` constraint on a `REF` column.

- You cannot use this clause to drop:

  - A pseudocolumn, cluster column, or partitioning column. You can drop nonpartitioning columns from a partitioned table if all the tablespaces where the partitions were created are online and in read/write mode.

  - A column from a nested table, an object table, or a table owned by `SYS`.

See Also: "Dropping a Column: Example"

rename_column_clause

Use the `rename_column_clause` to rename a column of `table`. The new column name must not be the same as any other column name in `table`.

When you rename a column, Oracle Database handles dependent objects as follows:

- Function-based indexes and check constraints that depend on the renamed column remain valid.

- Dependent views, triggers, domain indexes, functions, procedures, and packages are invalidated. Oracle Database attempts to revalidate them when they are next accessed, but you may need to alter these objects with the new column name if revalidation fails.

Restrictions on Renaming Columns Renaming columns is subject to the following restrictions:

- You cannot combine this clause with any of the other `column_clauses` in the same statement.

- You cannot rename a column that is used to define a join index. Instead you must drop the index, rename the column, and re-create the index.

See Also: "Renaming a Column: Example"

modify_collection_retrieval

Use the `modify_collection_retrieval` clause to change what Oracle Database returns when a collection item is retrieved from the database.

collection_item Specify the name of a column-qualified attribute whose type is nested table or varray.

RETURN AS Specify what Oracle Database should return as the result of a query:

- `LOCATOR` specifies that a unique locator for the nested table is returned.

- `VALUE` specifies that a copy of the nested table itself is returned.

  See Also: "Collection Retrieval: Example"

modify_LOB_storage_clause

The `modify_LOB_storage_clause` lets you change the physical attributes of `LOB_item`. You can specify only one `LOB_item` for each `modify_LOB_storage_clause`.

The `REBUILD` `FREEPOOLS` clause removes all the old data from the LOB column. This clause is useful only if you reverting to `PCTVERSION` for management of LOBs. You might want to do this to manage older data blocks.

Restrictions on Modifying LOB Storage Modifying LOB storage is subject to the following restrictions:

- You cannot modify the value of the `INITIAL` parameter in the `storage_clause` when modifying the LOB storage attributes.

- You cannot specify both the `allocate_extent_clause` and the `deallocate_unused_clause` in the same statement.

- You cannot specify both the `PCTVERSION` and `RETENTION` parameters.

See Also: LOB_storage_clause (in `CREATE` `TABLE`) for information on setting LOB parameters and "LOB Columns: Examples"

alter_varray_col_properties

The `alter_varray_col_properties` clause lets you change the storage characteristics of an existing LOB in which a varray is stored.

Restriction on Altering Varray Column Properties You cannot specify the `TABLESPACE` clause of `LOB_parameters` as part of this clause. The LOB tablespace for a varray defaults to the tablespace of the containing table.

REKEY encryption_spec

The `REKEY` clause causes the database to generate a new encryption key. All encrypted columns in the table are reencrypted using the new key and, if you specify the `USING` clause of the `encryption_spec`, a new encryption algorithm. You cannot combine this clause with any other clauses in this `ALTER` `TABLE` statement.

See Also: Oracle Advanced Security Administrator's Guide for more information on transparent column encryption

constraint_clauses

Use the `constraint_clauses` to add a new constraint using out-of-line declaration, modify the state of an existing constraint, or drop a constraint. Please refer to constraint for a description of all the keywords and parameters of out-of-line constraints and `constraint_state`.

Adding a Constraint

The `ADD` clause lets you add a new out-of-line constraint or out-of-line `REF` constraint to the table.

See Also: "Disabling a CHECK Constraint: Example", "Specifying Object Identifiers: Example", and "REF Columns: Examples"

Modifying a Constraint

The `MODIFY` `CONSTRAINT` clause lets you change the state of an existing constraint.

Restrictions on Modifying Constraints Modifying constraints is subject to the following restrictions:

- You cannot change the state of a `NOT` `DEFERRABLE` constraint to `INITIALLY` `DEFERRED`.

- If you specify this clause for an index-organized table, then you cannot specify any other clauses in the same statement.

See Also: "Changing the State of a Constraint: Examples"

Renaming a Constraint

The `RENAME` `CONSTRAINT` clause lets you rename any existing constraint on `table`. The new constraint name cannot be the same as any existing constraint on any object in the same schema. All objects that are dependent on the constraint remain valid.

See Also: "Renaming Constraints: Example"

drop_constraint_clause

The `drop_constraint_clause` lets you drop an integrity constraint from the database. Oracle Database stops enforcing the constraint and removes it from the data dictionary. You can specify only one constraint for each `drop_constraint_clause`, but you can specify multiple `drop_constraint_clauses` in one statement.

PRIMARY KEY Specify `PRIMARY` `KEY` to drop the primary key constraint of `table`.

UNIQUE Specify `UNIQUE` to drop the unique constraint on the specified columns.

If you drop the primary key or unique constraint from a column on which a bitmap join index is defined, then Oracle Database invalidates the index. See CREATE INDEX for information on bitmap join indexes.

CONSTRAINT Specify `CONSTRAINT` `constraint` to drop an integrity constraint other than a primary key or unique constraint.

CASCADE Specify `CASCADE` if you want all other integrity constraints that depend on the dropped integrity constraint to be dropped as well.

KEEP INDEX | DROP INDEX Specify `KEEP` `INDEX` or `DROP` `INDEX` to indicate whether Oracle Database should preserve or drop the index it has been using to enforce the `PRIMARY` `KEY` or `UNIQUE` constraint.

Restrictions on Dropping Constraints Dropping constraints is subject to the following restrictions:

- You cannot drop a primary key or unique key constraint that is part of a referential integrity constraint without also dropping the foreign key. To drop the referenced key and the foreign key together, use the `CASCADE` clause. If you omit `CASCADE`, then Oracle Database does not drop the primary key or unique constraint if any foreign key references it.

- You cannot drop a primary key constraint (even with the `CASCADE` clause) on a table that uses the primary key as its object identifier (OID).

- If you drop a referential integrity constraint on a `REF` column, then the `REF` column remains scoped to the referenced table.

- You cannot drop the scope of a `REF` column.

See Also: "Dropping Constraints: Examples"

alter_external_table_clauses

Use the `alter_external_table_clauses` to change the characteristics of an external table. This clause has no affect on the external data itself. The syntax and semantics of the `parallel_clause`, `enable_disable_clause`, `external_data_properties`, and `REJECT` `LIMIT` clause are the same as described for `CREATE` `TABLE`. See the external_table_clause (in `CREATE` `TABLE`).

PROJECT COLUMN Clause This clause lets you determine how the access driver validates the rows of an external table in subsequent queries. The default is `PROJECT` `COLUMN` `ALL`, which means that the access driver processes all column values, regardless of which columns are selected, and validates only those rows with fully valid column entries. If any column value would raise an error, such as a datatype conversion error, the row is rejected even if that column was not referenced in the select list. If you specify `PROJECT` `COLUMN` `REFERENCED`, then the access driver processes only those columns in the select list.

The `ALL` setting guarantees consistent result sets. The `REFERENCED` setting can result in different numbers of rows returned, depending on the columns referenced in subsequent queries, but is faster than the `ALL` setting. If a subsequent query selects all columns of the external table, then the settings behave identically.

Restrictions on Altering External Tables Altering external tables is subject to the following restrictions:

- You cannot modify an external table using any clause outside of this clause.

- You cannot add a `LONG`, varray, or object type column to an external table, nor can you change the datatype of an external table column to any of these datatypes.

- You cannot add a constraint to an external table.

- You cannot modify the storage parameters of an external table.

alter_table_partitioning

The clauses in this section apply only to partitioned tables. You cannot combine partition operations with other partition operations or with operations on the base table in the same `ALTER` `TABLE` statement.

Notes on Changing Table Partitioning The following notes apply when changing table partitioning:

- If you drop, exchange, truncate, move, modify, or split a partition on a table that is a master table for one or more materialized views, then existing bulk load information about the table will be deleted. Therefore, be sure to refresh all dependent materialized views before performing any of these operations.

- If a bitmap join index is defined on `table`, then any operation that alters a partition of `table` causes Oracle Database to mark the index `UNUSABLE`.

For additional information on partition operations on tables with an associated `CONTEXT` domain index, please refer to Oracle Text Reference.

The storage of partitioned database entities in tablespaces of different block sizes is subject to several restrictions. Please refer to Oracle Database Administrator's Guide for a discussion of these restrictions.

modify_table_default_attrs

The `modify_table_default_attrs` clause lets you specify new default values for the attributes of `table`. Partitions and LOB partitions you create subsequently will inherit these values unless you override them explicitly when creating the partition or LOB partition. Existing partitions and LOB partitions are not affected by this clause.

Only attributes named in the statement are affected, and the default values specified are overridden by any attributes specified at the individual partition or LOB partition level.

- `FOR` `PARTITION` applies only to composite-partitioned tables. This clause specifies new default values for the attributes of `partition`. Subpartitions and LOB subpartitions of `partition` that you create subsequently will inherit these values unless you override them explicitly when creating the subpartition or LOB subpartition. Existing subpartitions are not affected by this clause.

- `PCTTHRESHOLD`, `key_compression`, and the `alter_overflow_clause` are valid only for partitioned index-organized tables. In addition, in the `key_compression` clause, you cannot specify an integer after the `COMPRESS` keyword. Key compression length can be specified only when you create the table.

- You cannot specify the `PCTUSED` parameter in `segment_attributes` for the index segment of an index-organized table.

- You can specify the key compression only if key compression is already specified at the table level.

set_subpartition_template

Use the `set_subpartition_template` clause to create or replace existing default list or hash subpartition definitions for each table partition. This clause is valid only for composite-partitioned tables. It replaces the existing subpartition template or creates a new template if you have not previously created one. Existing subpartitions are not affected, nor are existing local and global indexes. However, subsequent partitioning operations (such as add and merge operations) will use the new template.

You can drop an existing subpartition template by specifying `ALTER` `TABLE` `table` `SET` `SUBPARTITION` `TEMPLATE` `()`.

Note: When you specify tablespace storage for the subpartition template, it does not override any tablespace storage you have specified explicitly for the partitions of `table`. To specify tablespace storage for subpartitions, do one of these things: Omit tablespace storage at the partition level and specify tablespace storage in the subpartition template. Define individual subpartitions with specific tablespace storage.

Restrictions on Subpartition Templates Please refer to "Restrictions on Subpartition Templates" in the documentation on `CREATE` `TABLE`.

modify_table_partition

The `modify_table_partition` clause lets you change the real physical attributes of a range, hash, or list partition. This clause optionally modifies the storage attributes of one or more LOB items for the partition. You can specify new values for physical attributes (with some restrictions, as noted in the sections that follow), logging, `and` storage parameters.

You can also specify how Oracle Database should handle local indexes that become unusable as a result of the modification to the partition. See "UNUSABLE LOCAL INDEXES Clauses".

In the `partition_attribute` clause, the `shrink_clause` lets you compact an individual partition segment. Please refer to shrink_clause for additional information on this clause.

For partitioned index-organized tables, you can also update the mapping table in conjunction with partition changes. See the alter_mapping_table_clauses .

See Also: "Modifying Table Partitions: Examples"

modify_range_partition

When modifying a range partition, if `table` is composite partitioned:

- If you specify the `allocate_extent_clause`, then Oracle Database allocates an extent for each subpartition of `partition`.

- If you specify the `deallocate_unused_clause`, then Oracle Database deallocates unused storage from each subpartition of `partition`.

- Any other attributes changed in this clause will be changed in subpartitions of `partition` as well, overriding existing values. To avoid changing the attributes of existing subpartitions, use the `FOR` `PARTITION` clause of `modify_table_default_attrs.`

Restriction on Modifying Range Partitions If you specify `UNUSABLE` `LOCAL` `INDEXES`, then you cannot specify any other clause of `modify_range_partition`.

add_hash_subpartition This clause is valid only for range-hash composite partitions. The `add_hash_subpartition` clause lets you add a hash subpartition to `partition`. Oracle Database populates the new subpartition with rows rehashed from the other subpartition(s) of `partition` as determined by the hash function. For optimal load balancing, the total number of subpartitions should be a power of 2.

- If you do not specify `subpartition`, then Oracle Database assigns a name in the form `SYS_SUBP` `n`.

- The `list_values_clause` is not valid for this operation.

- In the `partitioning_storage_clause`, the only clause you can specify for subpartitions is the `TABLESPACE` clause. If you do not specify `TABLESPACE`, then the new subpartition will reside in the default tablespace of `partition`.

Oracle Database adds local index partitions corresponding to the selected partition.

Oracle Database marks `UNUSABLE` the local index partitions corresponding to the added partitions. The database invalidates any indexes on heap-organized tables. You can update these indexes during this operation using the update_index_clauses.

The `update_index_clauses` are not needed, and are not valid, for partitioned index-organized tables. Index-organized tables are primary key based, so Oracle can keep global indexes `USABLE` during operations that move data but do not change its value.

add_list_subpartition The `add_list_subpartition` clause lets you add a list subpartition to partition. This clause is valid only for range-list composite partitions, and only if you have not already created a `DEFAULT` subpartition.

- If you do not specify `subpartition`, then Oracle Database assigns a name in the form `SYS_SUBP` `n`.

- The `list_values_clause` is required in this operation, and the values you specify in the `list_values_clause` cannot exist in any other subpartition of `partition`. However, these values can duplicate values found in subpartitions of other partitions.

- In the `partitioning_storage_clause`, the only clause you can specify for subpartitions is the `TABLESPACE` clause. If you do not specify `TABLESPACE`, then Oracle Database stores the new subpartition in the default tablespace of `partition`. If partition has no default tablespace, then the database uses the default tablespace of table. If table has no default tablespace, then the database uses the default tablespace of the user.

Oracle Database also adds a subpartition with the same value list to all local index partitions of the table. The status of existing local and global index partitions of `table` are not affected.

Restriction on Adding List Subpartitions You cannot specify this clause if you have already created a `DEFAULT` subpartition for this partition. Instead you must split the `DEFAULT` partition using the `split_list_subpartition` clause.

modify_hash_partition

When modifying a hash partition, in the `partition_attributes` clause, you can specify only the `allocate_extent_clause` and `deallocate_unused_clause`. All other attributes of the partition are inherited from the table-level defaults except `TABLESPACE`, which stays the same as it was at create time.

COALESCE SUBPARTITION `COALESCE` `SUBPARTITION` applies only to hash subpartitions. Use the `COALESCE` `SUBPARTITION` clause if you want Oracle Database to select the last hash subpartition, distribute its contents into one or more remaining subpartitions (determined by the hash function), and then drop the last subpartition.

Oracle Database drops local index partitions corresponding to the selected partition.

Oracle Database marks `UNUSABLE` the local index partitions corresponding to one or more absorbing partitions. The database invalidates any global indexes on heap-organized tables. You can update these indexes during this operation using the update_index_clauses.

The `update_index_clauses` are not needed, and are not valid, for partitioned index-organized tables. Index-organized tables are primary key based, so Oracle can keep global indexes `USABLE` during operations that move data but do not change its value.

Restrictions on Modifying Hash Partitions Modifying hash partitions is subject to the following restrictions:

- If you specify `UNUSABLE` `LOCAL` `INDEXES`, then you cannot specify any other clause of `modify_hash_partition`.

- If you update global indexes using the `update_all_indexes_clause`, you can specify only the keywords `UPDATE` `INDEXES`, not the subclause.

modify_list_partition

When modifying a list partition, the following additional clauses are available:

Restriction on Modifying List Partitions If you specify `UNUSABLE` `LOCAL` `INDEXES`, then you cannot specify any other clause of `modify_list_partition`.

ADD | DROP VALUES Clauses These clauses are valid only when you are modifying list partitions. Local and global indexes on the table are not affected by either of these clauses.

- Use the `ADD` `VALUES` clause to extend the `partition_value` list of `partition` to include additional values. The added partition values must comply with all rules and restrictions listed in the list_partitioning of `CREATE` `TABLE`.

- Use the `DROP` `VALUES` clause to reduce the `partition_value` list of `partition` by eliminating one or more `partition_value`. When you specify this clause, Oracle Database checks to ensure that no rows with this value exist. If such rows do exist, then Oracle Database returns an error.

Note: `ADD` `VALUES` and `DROP` `VALUES` operations on a table with a `DEFAULT` list partition are enhanced if you have defined a local prefixed index on the table.

Restrictions on Adding and Dropping List Values Adding and dropping list values are subject to the following restrictions:

- You cannot add values to a `default` list partition. If `table` contains a `default` partition and you attempt to add values to a nondefault partition, then Oracle Database will check that the values being added do not already exist in the `default` partition. If the values do exist in the `default` partition, then Oracle Database returns an error.

- You cannot drop values from a `default` partition.

modify_table_subpartition

This clause applies only to composite-partitioned tables.

modify_hash_subpartition

The `modify_hash_subpartition` clause lets you allocate or deallocate storage for an individual subpartition of `table`. This clause is valid only for range-hash composite-partitioned tables.

The `shrink_clause` lets you compact an individual subpartition segment. Please refer to shrink_clause for additional information on this clause.

You can also specify how Oracle Database should handle local indexes that become unusable as a result of the modification to the partition. See "UNUSABLE LOCAL INDEXES Clauses".

Restriction on Modifying Hash Subpartitions The only `modify_LOB_parameters` you can specify for `subpartition` are the `allocate_extent_clause` and `deallocate_unused_clause`.

modify_list_subpartition

The `modify_list_subpartition` clause lets you make the same changes to a list subpartition that you can make to a hash subpartition. In addition, it lets you add or remove values from the value list of a list subpartition. This clause is valid only for range-list composite-partitioned tables.

The `shrink_clause` lets you compact an individual subpartition segment. Please refer to shrink_clause for additional information on this clause.

ADD VALUES Specify `ADD` `VALUES` to extend the value list of `subpartition`.

- The values you specify cannot already exist in the value list of `subpartition` or of any other subpartition of the same partition. However, the values can exist in the value lists of subpartitions of other partitions.

- If you have defined a `DEFAULT` subpartition, then Oracle Database verifies that none of the values you are adding exist in rows of the `DEFAULT` subpartition. If the added values do exist in the `DEFAULT` subpartition, then the statement fails.

- Oracle Database adds corresponding values to the value list of any local index subpartitions. The status of local and global index partitions is not affected by this operation.

DROP VALUES Specify `DROP` `VALUES` to remove one or more values from the value list of `subpartition`.

- The values you specify must be a subset of existing values in `subpartition`.

- You cannot use this clause to drop all values in a subpartition. Instead you must use an `ALTER` `TABLE` ... `DROP` `SUBPARTITION` statement.

- If `subpartition` contains any rows containing one of the values being dropped, then the operation fails and Oracle Database returns an error. You must first delete any rows containing the values you want to drop before reissuing the statement.

- Oracle Database updates the value list of any corresponding local index subpartitions. The status of local and global index partitions is not affected by this operation.

You can also specify how Oracle Database should handle local indexes that become unusable as a result of the modification to the partition. See "UNUSABLE LOCAL INDEXES Clauses".

Restriction on Modifying List Subpartitions The only `modify_LOB_parameters` you can specify for `subpartition` are the `allocate_extent_clause` and `deallocate_unused_clause`.

move_table_partition

Use the `move_table_partition` clause to move `partition` to another segment. You can move partition data to another tablespace, recluster data to reduce fragmentation, or change create-time physical attributes.

If the table contains LOB columns, then you can use the `LOB_storage_clause` to move the LOB data and LOB index segments associated with this partition. Only the LOBs named are affected. If you do not specify the `LOB_storage_clause` for a particular LOB column, then its LOB data and LOB index segments are not moved.

Oracle Database moves local index partitions corresponding to the specified partition. If the moved partitions are not empty, then the database marks them `UNUSABLE`. The database invalidates global indexes on heap-organized tables. You can update these indexes during this operation using the update_index_clauses.

The `update_index_clauses` are not needed, and are not valid, for partitioned index-organized tables. Index-organized tables are primary key based, so Oracle can keep global indexes `USABLE` during operations that move data but do not change its value.

When you move a LOB data segment, Oracle Database drops the old data segment and corresponding index segment and creates new segments even if you do not specify a new tablespace.

The move operation obtains its parallel attribute from the `parallel_clause`, if specified. If not specified, the default parallel attributes of the table, if any, are used. If neither is specified, then Oracle Database performs the move serially.

Specifying the `parallel_clause` in `MOVE` `PARTITION` does not change the default parallel attributes of `table`.

Note: For index-organized tables, Oracle Database uses the address of the primary key, as well as its value, to construct logical rowids. The logical rowids are stored in the secondary index of the table. If you move a partition of an index-organized table, then the address portion of the rowids will change, which can hamper performance. To ensure optimal performance, rebuild the secondary index(es) on the moved partition to update the rowids.

See Also: Oracle Database Concepts for more information on logical rowids and "Moving Table Partitions: Example"

MAPPING TABLE The `MAPPING` `TABLE` clause is relevant only for an index-organized table that already has a mapping table defined for it. Oracle Database moves the mapping table along with the moved index-organized table partition. The mapping table partition inherits the physical attributes of the moved index-organized table partition. This is the only way you can change the attributes of the mapping table partition. If you omit this clause, the mapping table partition retains its original attributes.

Oracle Database marks `UNUSABLE` all corresponding bitmap index partitions.

Please refer to the mapping_table_clauses (in `CREATE` `TABLE`) for more information on this clause.

Restrictions on Moving Table Partitions Moving table partitions is subject to the following restrictions:

- If `partition` is a hash partition, then the only attribute you can specify in this clause is `TABLESPACE`.

- You cannot specify this clause for a partition containing subpartitions. However, you can move subpartitions using the `move_table_subpartition` clause.

move_table_subpartition

Use the `move_table_subpartition` clause to move `subpartition` to another segment. If you do not specify `TABLESPACE`, then the subpartition remains in the same tablespace.

If the subpartition is not empty, then Oracle Database marks `UNUSABLE` all local index subpartitions corresponding to the subpartition being moved. You can update all indexes on heap-organized tables during this operation using the update_index_clauses.

The `update_index_clauses` are not needed, and are not valid, for partitioned index-organized tables. Index-organized tables are primary key based, so Oracle can keep global indexes `USABLE` during operations that move data but do not change its value.

If the table contains LOB columns, then you can use the `LOB_storage_clause` to move the LOB data and LOB index segments associated with this subpartition. Only the LOBs specified are affected. If you do not specify the `LOB_storage_clause` for a particular LOB column, then its LOB data and LOB index segments are not moved.

When you move a LOB data segment, Oracle Database drops the old data segment and corresponding index segment and creates new segments even if you do not specify a new tablespace.

Restriction on Moving Table Subpartitions In `subpartition_spec`, the only clause of the `partitioning_storage_clause` you can specify is the `TABLESPACE` clause.

add_table_partition

Use the `add_table_partition` clause to add a hash, range, or list partition to `table`.

Oracle Database adds to any local index defined on `table` a new partition with the same name as that of the base table partition. If the index already has a partition with such a name, then Oracle Database generates a partition name of the form `SYS_P` `n`.

If `table` is index organized, then Oracle Database adds a partition to any mapping table and overflow area defined on the table as well.

See Also: "Adding a Table Partition with a LOB: Examples"

add_range_partition_clause

The `add_range_partition_clause` lets you add a new range partition to the high end of a partitioned table (after the last existing partition). You can specify any create-time physical attributes for the new partition. If the table contains LOB columns, then you can also specify partition-level attributes for one or more LOB items.

If you do not specify a new `partition_name`, then Oracle Database assigns a name of the form `SYS_P` `n`. If you add a range partition to a composite-partitioned table and do not describe the subpartitions, then the database assigns subpartition names as described in partition_level_subpartition.

If a domain index is defined on `table`, then the index must not be marked `IN_PROGRESS` or `FAILED`.

A table can have up to 64K-1 partitions.

Restrictions on Adding Range Partitions Adding range partitions is subject to the following restrictions:

- If the upper partition bound of each partitioning key in the existing high partition is `MAXVALUE`, then you cannot add a partition to the table. Instead, use the `split_table_partition` clause to add a partition at the beginning or the middle of the table.

- The `key_compression` and `OVERFLOW` clauses are valid only for a partitioned index-organized table. You can specify `OVERFLOW` only if the partitioned table already has an overflow segment. You can specify key compression only if key compression is enabled at the table level.

- You cannot specify the `PCTUSED` parameter for the index segment of an index-organized table.

range_values_clause Specify the upper bound for the new partition. The `value_list` is a comma-delimited, ordered list of literal values corresponding to `column_list`. The `value_list` must collate greater than the partition bound for the highest existing partition in the table.

partition_level_subpartition The `partition_level_subpartition` clause (in `table_partition_description`) is valid only for a composite-partitioned table. This clause lets you specify hash or list subpartitions for a new range-hash or range-list composite partition. This clause overrides any subpartition descriptions defined in `subpartition_template` at the table level.

Notes on Composite Partitions Please refer to partition_level_subpartition in the documentation of `CREATE` `TABLE` for additional notes on this clause. Please refer to CREATE TABLE for more information.

Oracle Database adds a new index partition with the same subpartition descriptions to all local indexes defined on `table`. Global indexes defined on `table` are not affected.

add_hash_partition_clause

The `add_hash_partition_clause` lets you add a new hash partition to the high end of a partitioned table. Oracle Database populates the new partition with rows rehashed from other partitions of `table` as determined by the hash function. For optimal load balancing, the total number of partitions should be a power of 2.

You can specify a name for the partition, and optionally a tablespace where it should be stored. If you do not specify a name, then the database assigns a partition name of the form `SYS_P` `n`. If you do not specify `TABLESPACE`, then the new partition is stored in the default tablespace of the table. Other attributes are always inherited from table-level defaults.

If this operation causes data to be rehashed among partitions, then the database marks `UNUSABLE` any corresponding local index partitions. You can update all indexes on heap-organized tables during this operation using the update_index_clauses.

The `update_index_clauses` are not needed, and are not valid, for partitioned index-organized tables. Index-organized tables are primary key based, so Oracle can keep global indexes `USABLE` during operations that move data but do not change its value.

Use the `parallel_clause` to specify whether to parallelize the creation of the new partition.

See Also: CREATE TABLE and Oracle Database Concepts for more information on hash partitioning

Restriction on Adding Hash Partitions In `table_partition_description`, you cannot specify `partition_level_subpartition`.

add_list_partition_clause

The `add_list_partition_clause` lets you add a new partition to `table` using a new set of partition values. You can specify any create-time physical attributes for the new partition. If the table contains LOB columns, then you can also specify partition-level attributes for one or more LOB items.

When you add a list partition to a table, Oracle Database adds a corresponding index partition with the same value list to all local indexes defined on the table. Global indexes are not affected.

Restrictions on Adding List Partitions Adding list partitions is subject to the following restrictions:

- In `table_partition_description`, you cannot specify `partition_level_subpartition`.

- You cannot add a list partition if you have already defined a `DEFAULT` partition for the table. Instead you must use the `split_table_partition` clause to split the `DEFAULT` partition.

See Also: list_partitioning of `CREATE` `TABLE` for more information and restrictions on list partitions "Working with Default List Partitions: Example"

coalesce_table_partition

`COALESCE` applies only to hash partitions. Use the `coalesce_table_partition` clause to indicate that Oracle Database should select the last hash partition, distribute its contents into one or more remaining partitions as determined by the hash function, and then drop the last partition.

Oracle Database drops local index partitions corresponding to the selected partition. The database marks `UNUSABLE` the local index partitions corresponding to one or more absorbing partitions. The database invalidates any indexes on heap-organized tables. You can update all indexes during this operation using the update_index_clauses.

The `update_index_clauses` are not needed, and are not valid, for partitioned index-organized tables. Index-organized tables are primary key based, so Oracle can keep global indexes `USABLE` during operations that move data but do not change its value.

Restriction on Coalescing Table Partitions If you update global indexes using the `update_all_indexes_clause`, then you can specify only the keywords `UPDATE` `INDEXES`, not the subclause.

drop_table_partition

The `drop_table_partition` clause removes `partition`, and the data in that partition, from a partitioned table. If you want to drop a partition but keep its data in the table, then you must merge the partition into one of the adjacent partitions.

See Also: merge_table_partitions

If `table` has LOB columns, then Oracle Database also drops the LOB data and LOB index partitions and any subpartitions corresponding to `partition`.

If `table` is index organized and has a mapping table defined on it, then the database drops the corresponding mapping table partition as well.

Oracle Database drops local index partitions and subpartitions corresponding to `partition`, even if they are marked `UNUSABLE`.

You can update indexes on `table` during this operation using the update_index_clauses. If you specify the `parallel_clause` with the `update_index_clauses`, then the database parallelizes the index update, not the drop operation.

If you drop a range partition and later insert a row that would have belonged to the dropped partition, then the database stores the row in the next higher partition. However, if that partition is the highest partition, then the insert will fail, because the range of values represented by the dropped partition is no longer valid for the table.

Restrictions on Dropping Table Partitions Dropping table partitions is subject to the following restrictions:

- You cannot drop a partition of a hash-partitioned table. Instead, use the `coalesce_table_partition` clause.

- If `table` contains only one partition, then you cannot drop the partition. You must drop the table.

- If you update global indexes using the update_index_clauses, then you can specify only the `UPDATE` `INDEXES` keywords but not the subclause.

See Also: "Dropping a Table Partition: Example"

drop_table_subpartition

Use this clause to drop a list subpartition from a range-list composite-partitioned table. Oracle Database deletes any rows in the dropped subpartition.

Oracle Database drops the corresponding subpartition of any local index. Other index subpartitions are not affected. Any global indexes are marked `UNUSABLE` unless you specify the `update_global_index_clause` or `update_all_indexes_clause`.

Restrictions on Dropping Table Subpartitions Dropping table subpartitions is subject to the following restrictions:

- You cannot drop a hash subpartition. Instead use the `MODIFY` `PARTITION` ... `COALESCE` `SUBPARTITION` syntax.

- You cannot drop the last subpartition of a partition. Instead use the `drop_table_partition` clause.

- If you update the global indexes, you cannot specify the optional subclause of the `update_all_indexes_clause`.

rename_partition_subpart

Use the `rename_table_partition` clause to rename a table partition or subpartition `current_name` to `new_name`. For both partitions and subpartitions, `new_name` must be different from all existing partitions and subpartitions of the same table.

If `table` is index organized, then Oracle Database assigns the same name to the corresponding primary key index partition as well as to any existing overflow partitions and mapping table partitions.

See Also: "Renaming Table Partitions: Examples"

truncate_partition_subpart

Specify `TRUNCATE` `PARTITION` to remove all rows from `partition` or, if the table is composite partitioned, all rows from the subpartitions of `partition`. Specify `TRUNCATE` `SUBPARTITION` to remove all rows from `subpartition`. If `table` is index organized, then Oracle Database also truncates any corresponding mapping table partitions and overflow area partitions.

If the partition or subpartition to be truncated contains data, then you must first disable any referential integrity constraints on the table. Alternatively, you can delete the rows and then truncate the partition.

If `table` contains any LOB columns, then the LOB data and LOB index segments for this partition are also truncated. If `table` is composite partitioned, then the LOB data and LOB index segments for the subpartitions of the partition are truncated.

If a domain index is defined on `table`, then the index must not be marked `IN_PROGRESS` or `FAILED`, and the index partition corresponding to the table partition being truncated must not be marked `IN_PROGRESS`.

For each partition or subpartition truncated, Oracle Database also truncates corresponding local index partitions and subpartitions. If those index partitions or subpartitions are marked `UNUSABLE`, then the database truncates them and resets the `UNUSABLE` marker to `VALID`.

You can update global indexes on `table` during this operation using the update_global_index_clause or the update_all_indexes_clause. If you specify the `parallel_clause` with one of these clauses, then the database parallelizes the index update, not the truncate operation.

DROP STORAGE Specify `DROP` `STORAGE` to deallocate space from the deleted rows and make it available for use by other schema objects in the tablespace.

REUSE STORAGE Specify `REUSE` `STORAGE` to keep space from the deleted rows allocated to the partition or subpartition. The space is subsequently available only for inserts and updates to the same partition or subpartition.

See Also: "Truncating Table Partitions: Example"

Restriction on Truncating Table Partitions and Subpartitions If you update global indexes using the `update_all_indexes_clause`, then you can specify only the `UPDATE` `INDEXES` keywords, not the subclause.

split_table_partition

The `split_table_partition` clause lets you create, from `current_partition`, two new partitions, each with a new segment and new physical attributes, and new initial extents. The segment associated with `current_partition` is discarded.

The new partitions inherit all unspecified physical attributes from `current_partition`.

Note: Oracle Database can optimize and speed up `SPLIT` `PARTITION` and `SPLIT` `SUBPARTITION` operations if specific conditions are met. Please refer to Oracle Database Administrator's Guide for information on optimizing these operations.

If you split a `DEFAULT` list partition, then the first of the resulting partitions will have the split values, and the second resulting partition will have the `DEFAULT` value.

If `table` is index organized, then Oracle Database splits any corresponding mapping table partition and places it in the same tablespace as the parent index-organized table partition. The database also splits any corresponding overflow area, and you can use the `OVERFLOW` clause to specify segment attributes for the new overflow areas.

Oracle Database splits the corresponding local index partition, even if it is marked `UNUSABLE`. The database marks `UNUSABLE`, and you must rebuild the local index partitions corresponding to the split partitions. The new index partitions inherit their attributes from the partition being split. The database stores the new index partitions in the default tablespace of the index partition being split. If that index partition has no default tablespace, then the database uses the tablespace of the new underlying table partitions.

If `table` contains LOB columns, then you can use the `LOB_storage_clause` to specify separate LOB storage attributes for the LOB data segments resulting from the split. The database drops the LOB data and LOB index segments of `current_partition` and creates new segments for each LOB column, for each partition, even if you do not specify a new tablespace.

AT Clause The `AT` clause applies only to range partitions. Specify the new noninclusive upper bound for the first of the two new partitions. The value list must compare less than the original partition bound for `current_partition` and greater than the partition bound for the next lowest partition (if there is one).

VALUES Clause The `VALUES` clause applies only to list partitions. Specify the partition values you want to include in the first of the two new partitions. Oracle Database creates the first new partition using the partition value list you specify and creates the second new partition using the remaining partition values from `current_partition`. Therefore, the value list cannot contain all of the partition values of `current_partition`, nor can it contain any partition values that do not already exist for `current_partition`.

INTO Clause The `INTO` clause lets you describe the two partitions resulting from the split. In `partition_spec`, the keyword `PARTITION` is required even if you do not specify the optional names and physical attributes of the two partitions resulting from the split. If you do not specify new partition names, then Oracle Database assigns names of the form `SYS_P` `n`. Any attributes you do not specify are inherited from `current_partition`.

For range-hash composite-partitioned tables, if you specify subpartitioning for the new partitions, then you can specify only `TABLESPACE` for the subpartitions. All other attributes are inherited from `current_partition`. If you do not specify subpartitioning for the new partitions, then their tablespace is also inherited from `current_partition`.

For range-list composite-partitioned tables, you cannot specify subpartitions for the new partitions at all (using the `partition_level_subpartition` clause of `table_partition_description`). The subpartitions of the split partition will inherit the number of subpartitions and value lists from c`urrent_partition`.

For all range-list composite-partitioned tables, and for range-hash composite-partitioned tables for which you do not specify subpartition names for the newly created subpartitions, the newly created subpartitions inherit their names from the parent partition as follows:

- For those subpartitions in the parent partition with names of the form `partition_name` underscore (_) `subpartition_name` (for example, `P1_SUBP1`), Oracle Database generates corresponding names in the newly created subpartitions using the new partition names (for example `P1A_SUB1` and `P1B_SUB1`).

- For those subpartitions in the parent partition with names of any other form, Oracle Database generates subpartition names of the form `SYS_SUBP` `n`.

Oracle Database splits the corresponding partition in each local index defined on `table`, even if the index is marked `UNUSABLE`.

Oracle Database invalidates any indexes on heap-organized tables. You can update these indexes during this operation using the update_index_clauses.

The `update_index_clauses` are not needed, and are not valid, for partitioned index-organized tables. Index-organized tables are primary key based, so Oracle can keep global indexes `USABLE` during operations that move data but do not change its value.

The `parallel_clause` lets you parallelize the split operation but does not change the default parallel attributes of the table.

Restrictions on Splitting Table Partitions Splitting table partitions is subject to the following restrictions:

- You cannot specify this clause for a hash subpartition.

- In `subpartition_spec`, the only clause of `partitioning_storage_clause` you can specify is `TABLESPACE`.

split_table_subpartition

Use this clause to split a list subpartition into two separate subpartitions with nonoverlapping value lists.

Note: Oracle Database can optimize and speed up `SPLIT` `PARTITION` and `SPLIT` `SUBPARTITION` operations if specific conditions are met. Please refer to Oracle Database Administrator's Guide for information on optimizing these operations.

- In the `VALUES` clause, specify the subpartition values you want to include in the first of the two new subpartitions. You can specify `NULL` if you have not already specified `NULL` for another subpartition in the same partition. Oracle Database creates the first new subpartition using the subpartition value list you specify and creates the second new partition using the remaining partition values from the current subpartition. Therefore, the value list cannot contain all of the partition values of the current subpartition, nor can it contain any partition values that do not already exist for the current subpartition.

- The `INTO` clause lets you describe the two subpartitions resulting from the split. In `subpartition_spec`, the keyword `SUBPARTITION` is required even if you do not specify the optional names and attributes of the two new subpartitions. If you do not specify new subpartition names, or if you omit this clause entirely, then Oracle Database assigns names of the form `SYS_SUBP` `n`. Any attributes you do not specify are inherited from the current subpartition.

Oracle Database splits any corresponding local subpartition index, even if it is marked `UNUSABLE`. The new index subpartitions inherit the names of the new table subpartitions unless those names are already held by index subpartitions. In that case, the database assigns new index subpartition names of the form `SYS_SUBPn`. The new index subpartitions inherit physical attributes from the parent subpartition. However, if the parent subpartition does not have a default `TABLESPACE` attribute, then the new subpartitions inherit the tablespace of the corresponding new table subpartitions.

Oracle Database invalidates indexes on heap-organized tables. You can update these indexes by using the update_index_clauses.

The `update_index_clauses` are not needed, and are not valid, for partitioned index-organized tables. Index-organized tables are primary key based, so Oracle can keep global indexes `USABLE` during operations that move data but do not change its value.

Restrictions on Splitting Table Subpartitions Splitting table subpartitions is subject to the following restrictions:

- You cannot specify this clause for a hash subpartition.

- In `subpartition_spec`, the only clause of `partitioning_storage_clause` you can specify is `TABLESPACE`.

merge_table_partitions

The `merge_table_partitions` clause lets you merge the contents of two partitions of `table` into one new partition and then drop the original two partitions.

- The two partitions to be merged must be adjacent if they are range partitions. The new partition inherits the partition bound of the higher of the two original partitions.

- List partitions need not be adjacent in order to be merged. When you merge two list partitions, the resulting partition value list is the union of the set of the two partition values lists of the partitions being merged. If you merge a `DEFAULT` list partition with another list partition, then the resulting partition will be the `DEFAULT` partition and will have the `DEFAULT` value.

- When you merge two range-list composite partitions, you cannot specify the `partition_level_subpartition`. Oracle Database obtains the subpartitioning information from any subpartition template. If you have not specified a subpartition template, then the database creates one `DEFAULT` subpartition.

Any attributes not specified in the `segment_attributes_clause` are inherited from table-level defaults.

If you do not specify a new `partition_name`, then Oracle Database assigns a name of the form `SYS_P` `n`. If the new partition has subpartitions, then the database assigns subpartition names as described in partition_level_subpartition.

Oracle Database drops local index partitions corresponding to the selected partitions and marks `UNUSABLE` the local index partition corresponding to merged partition. The database also marks `UNUSABLE` any global indexes on heap-organized tables. You can update all these indexes during this operation using the update_index_clauses.

The `update_index_clauses` are not needed, and are not valid, for partitioned index-organized tables. Index-organized tables are primary key based, so Oracle can keep global indexes `USABLE` during operations that move data but do not change its value.

Restriction on Merging Table Partitions You cannot specify this clause for a hash-partitioned table. Instead, use the `coalesce_table_partition` clause.

See Also: "Merging Two Table Partitions: Example" and "Working with Default List Partitions: Example"

partition_level_subpartition The `partition_level_subpartition` clause is valid only when you are merging range-hash composite partitions. This clause lets you specify subpartitioning attributes for the newly merged partition. Any attributes not specified in this clause are inherited from table-level values. If you do not specify this clause, then the new merged partition inherits subpartitioning attributes from table-level defaults.

If you omit this clause, then the new partition inherits the subpartition descriptions from any subpartition template you have defined. If you have not defined a subpartition template, then Oracle Database creates one subpartition in the newly merged partition.

Specify the `parallel_clause` to parallelize the merge operation.

Restriction on the partition_level_subpartition Clause You cannot specify this clause for a range-list composite partition.

merge_table_subpartitions

The `merge_table_subpartitions` clause lets you merge the contents of two list subpartitions of `table` into one new subpartition and then drop the original two subpartitions. The two subpartitions to be merged must belong to the same partition, but they do not have to be adjacent. The data in the resulting subpartition consists of the combined data from the merged subpartitions.

- If you do not specify a new subpartition name, or if you omit the `INTO` clause entirely, then Oracle Database assigns a name of the form `SYS_SUBP` `n`.

- If you do specify the `INTO` clause, then the keyword `SUBPARTITION` in `subpartition_spec` is required, you cannot specify the `list_values_clause`, and the only clause you can specify in the `partitioning_storage_clause` is the `TABLESPACE` clause.

Any attributes you do not specify explicitly for the new subpartition are inherited from partition-level values. If you reuse one of the subpartition names for the new subpartition, then the new subpartition inherits values from the subpartition whose name is being reused rather than from partition-level default values.

Oracle Database merges corresponding local index subpartitions and marks the resulting index subpartition `UNUSABLE`. The database also marks `UNUSABLE` both partitioned and nonpartitioned global indexes on heap-organized tables. You can update all indexes during this operation using the update_index_clauses.

The `update_index_clauses` are not needed, and are not valid, for partitioned index-organized tables. Index-organized tables are primary key based, so Oracle can keep global indexes `USABLE` during operations that move data but do not change its value.

Restriction on Merging Table Subpartitions You cannot specify this clause for a hash subpartition.

exchange_partition_subpart

Use the `EXCHANGE` `PARTITION` or `EXCHANGE` `SUBPARTITION` clause to exchange the data and index segments of:

- One nonpartitioned table with:

  - one hash, list, or range partition

  - one hash or list subpartition

- One hash-partitioned table with the hash subpartitions of a range partition of a range-hash composite-partitioned table

- One list-partitioned table with the list subpartitions of a range partition of a range-list composite-partitioned table.

In all cases, the structure of the table and the partition or subpartition being exchanged, including their partitioning keys, must be identical. In the case of list partitions and subpartitions, the corresponding value lists must also match.

This clause facilitates high-speed data loading when used with transportable tablespaces.

See Also: Oracle Database Administrator's Guide for information on transportable tablespaces

If `table` contains LOB columns, then for each LOB column Oracle Database exchanges LOB data and LOB index partition or subpartition segments with corresponding LOB data and LOB index segments of `table`.

All of the segment attributes of the two objects (including tablespace and logging) are also exchanged.

All statistics of the table and partition are exchanged, including table, column, index statistics, and histograms. Oracle Database recalculates the aggregate statistics of the table receiving the new partition.

Oracle Database invalidates any global indexes on the objects being exchanged. You can update the global indexes on the table whose partition is being exchanged by using either the update_global_index_clause or the update_all_indexes_clause clause. For the `update_all_indexes_clause`, you can specify only the keywords `UPDATE` `INDEXES`, not the subclause. Global indexes on the table being exchanged remain invalidated. If you specify the `parallel_clause` with either of these clauses, then the database parallelizes the index update, not the exchange operation.

See Also: "Notes on Exchanging Partitions and Subpartitions"

WITH TABLE table Specify the table with which the partition or subpartition will be exchanged.

INCLUDING INDEXES Specify `INCLUDING` `INDEXES` if you want local index partitions or subpartitions to be exchanged with the corresponding table index (for a nonpartitioned table) or local indexes (for a hash-partitioned table).

EXCLUDING INDEXES Specify `EXCLUDING` `INDEXES` if you want all index partitions or subpartitions corresponding to the partition and all the regular indexes and index partitions on the exchanged table to be marked `UNUSABLE`.

WITH VALIDATION Specify `WITH` `VALIDATION` if you want Oracle Database to return an error if any rows in the exchanged table do not map into partitions or subpartitions being exchanged.

WITHOUT VALIDATION Specify `WITHOUT` `VALIDATION` if you do not want Oracle Database to check the proper mapping of rows in the exchanged table.

Notes on Exchanging Partitions and Subpartitions The following notes apply when exchanging partitions and subpartitions:

- Both tables involved in the exchange must have the same primary key, and no validated foreign keys can be referencing either of the tables unless the referenced table is empty.

- When exchanging between a partitioned table and the range partition of a composite-partitioned table:

  - The partitioning key of the partitioned table must be identical to the subpartitioning key of the composite-partitioned table.

  - The number of partitions in the partitioned table must be identical to the number of subpartitions in the range partition of the composite-partitioned table.

  - If you are exchanging a list-partitioned table with a range-list partition of a composite-partitioned table, then the values list of the list partitions must exactly match the values list of the range-list subpartitions.

- When exchanging partitioned index-organized tables:

  - The source and target table or partition must have their primary key set on the same columns, in the same order.

  - If key compression is enabled, then it must be enabled for both the source and the target, and with the same prefix length.

  - Both the source and target must be index organized.

  - Both the source and target must have overflow segments, or neither can have overflow segments. Also, both the source and target must have mapping tables, or neither can have a mapping table.

  - Both the source and target must have identical storage attributes for any LOB columns.

See Also: "Exchanging Table Partitions: Example"

exceptions_clause This clause is valid only if the partitioned table has been defined with a `UNIQUE` constraint, and that constraint must be in `DISABLE` `VALIDATE` state. Specify a table into which Oracle Database places the rowids of all rows violating the constraint. If you omit `schema`, then the database assumes the exceptions table is in your own schema. If you omit this clause altogether, then the database assumes that the table is named `EXCEPTIONS`. The exceptions table must be on your local database.

You can create the `EXCEPTIONS` table using one of these scripts:

- `UTLEXCPT.SQL` uses physical rowids. Therefore it can accommodate rows from conventional tables but not from index-organized tables.

- `UTLEXPT1.SQL` uses universal rowids, so it can accommodate rows from both heap-organized and index-organized tables.

If you create your own exceptions table, then it must follow the format prescribed by one of these two scripts.

If you are collecting exceptions from index-organized tables based on primary keys (rather than universal rowids), then you must create a separate exceptions table for each index-organized table to accommodate its primary key storage. You create multiple exceptions tables with different names by modifying and resubmitting the script.

Restriction on Specifying an Exceptions Table This clause is not valid exchanging subpartitions.

See Also: The `DBMS_IOT` package in PL/SQL Packages and Types Reference for information on the SQL scripts Oracle Database Performance Tuning Guide for information on eliminating migrated and chained rows Oracle Database Upgrade Guide for compatibility issues related to the use of these scripts constraint for more information on constraint checking and "Creating an Exceptions Table for Index-Organized Tables: Example"

UNUSABLE LOCAL INDEXES Clauses

These two clauses modify the attributes of local index partitions and index subpartitions corresponding to `partition`, depending on whether you are modifying a partition or subpartition.

- `UNUSABLE` `LOCAL` `INDEXES` marks `UNUSABLE` the local index partition or index subpartition associated with `partition`.

- `REBUILD` `UNUSABLE` `LOCAL` `INDEXES` rebuilds the unusable local index partition or index subpartition associated with `partition`.

Restrictions on UNUSABLE LOCAL INDEXES This clause is subject to the following restrictions:

- You cannot specify this clause with any other clauses of the `modify_table_partition` clause.

- You cannot specify this clause in the `modify_table_partition` clause for a partition that has subpartitions. However, you can specify this clause in the `modify_hash_subpartition` or `modify_list_subpartition` clause.

update_index_clauses

Use the `update_index_clauses` to update the indexes on `table` as part of the table partitioning operation. When you perform DDL on a table partition, if an index is defined on `table`, then Oracle Database invalidates the entire index, not just the partitions undergoing DDL. This clause lets you update the index partition you are changing during the DDL operation, eliminating the need to rebuild the index after the DDL.

update_all_indexes_clause

Use this clause to update all indexes on `table`.

update_index_partition This clause is valid only for operations on table partitions and affects only local indexes.

- The `index_partition_description` lets you specify physical attributes, tablespace storage, and logging for each partition of each local index. If you specify only the `PARTITION` keyword, then Oracle Database updates the index partition as follows:

  - For operations on a single table partition (such as `MOVE` `PARTITION` and `SPLIT` `PARTITION`), the corresponding index partition inherits the attributes of the affected index partition. Oracle Database does not generate names for new index partitions, so any new index partitions resulting from this operation inherit their names from the corresponding new table partition.

  - For `MERGE` `PARTITION` operations, the resulting local index partition inherits its name from the resulting table partition and inherits its attributes from the local index.

- For a composite-partitioned index, the `index_subpartition_clause` lets you specify tablespace storage for each subpartition. Please refer to the index_subpartition_clause (in `CREATE` `INDEX`) for more information on this component of the `update_index_partition` clause.

update_index_subpartition This clause is valid only for operations on subpartitions of composite-partitioned tables and affects only local indexes on composite-partitioned tables. It lets you specify tablespace storage for one or more subpartitions.

Restriction on Updating All Indexes You cannot specify this clause for index-organized tables.

update_global_index_clause

Use this clause to update only global indexes on `table`. Oracle Database marks `UNUSABLE` all local indexes on `table`.

UPDATE GLOBAL INDEXES Specify `UPDATE` `GLOBAL` `INDEXES` to update the global indexes defined on `table`.

Restriction on Updating Global Indexes If the global index is a global domain index defined on a LOB column, then Oracle Database marks the domain index `UNUSABLE` instead of updating it.

INVALIDATE GLOBAL INDEXES Specify `INVALIDATE` `GLOBAL` `INDEXES` to invalidate the global indexes defined on `table`.

If you specify neither, then Oracle Database invalidates the global indexes.

Restrictions on Invalidating Global Indexes This clause is supported only for global indexes. It is not supported for index-organized tables. In addition, this clause updates only indexes that are `USABLE` and `VALID`. `UNUSABLE` indexes are left unusable, and `INVALID` global indexes are ignored.

See Also: "Updating Global Indexes: Example"

parallel_clause

The `parallel_clause` lets you change the default degree of parallelism for queries and DML on the table.

For complete information on this clause, please refer to parallel_clause in the documentation on `CREATE` `TABLE`.

Restrictions on Changing Table Parallelization Changing parallelization is subject to the following restrictions:

- If `table` contains any columns of LOB or user-defined object type, then subsequent `INSERT`, `UPDATE`, and `DELETE` operations on `table` are executed serially without notification. Subsequent queries, however, are executed in parallel.

- If you specify the `parallel_clause` in conjunction with the `move_table_clause`, then the parallelism applies only to the move, not to subsequent DML and query operations on the table.

See Also: "Specifying Parallel Processing: Example"

move_table_clause

The `move_table_clause` lets you relocate data of a nonpartitioned table or of a partition of a partitioned table into a new segment, optionally in a different tablespace, and optionally modify any of its storage attributes.

You can also move any LOB data segments associated with the table or partition using the `LOB_storage_clause` and `varray_col_properties` clause. LOB items not specified in this clause are not moved.

If you move the table to a different tablespace and the `COMPATIBLE` parameter is set to 10.0 or higher, Oracle Database leaves the storage table of any nested table columns in the tablespace in which it was created. If `COMPATIBLE` is set to any value less than 10.0, then the database silently moves the storage table to the new tablespace along with the table.

index_org_table_clause

For an index-organized table, the `index_org_table_clause` of the `move_table_clause` lets you additionally specify overflow segment attributes. The `move_table_clause` rebuilds the primary key index of the index-organized table. The overflow data segment is not rebuilt unless the `OVERFLOW` keyword is explicitly stated, with two exceptions:

- If you alter the values of `PCTTHRESHOLD` or the `INCLUDING` column as part of this `ALTER` `TABLE` statement, then the overflow data segment is rebuilt.

- If you explicitly move any of out-of-line columns (LOBs, varrays, nested table columns) in the index-organized table, then the overflow data segment is also rebuilt.

The index and data segments of LOB columns are not rebuilt unless you specify the LOB columns explicitly as part of this `ALTER` `TABLE` statement.

ONLINE Clause This clause is valid only for index-organized tables. Specify `ONLINE` if you want DML operations on the index-organized table to be allowed during rebuilding of the primary key index of the table.

Restrictions on Moving Tables Online Moving tables online is subject to the following restrictions:

- You cannot combine this clause with any other clause in the same statement.

- You cannot specify this clause for a partitioned index-organized table.

- Parallel DML is not supported during online `MOVE`. If you specify `ONLINE` and then issue parallel DML statements, then Oracle Database returns an error.

mapping_table_clause Specify `MAPPING` `TABLE` if you want Oracle Database to create a mapping table if one does not already exist. If it does exist, then the database moves the mapping table along with the index-organized table, and marks any bitmapped indexes `UNUSABLE`. The new mapping table is created in the same tablespace as the parent table.

Specify `NOMAPPING` to instruct the database to drop an existing mapping table.

Please refer to mapping_table_clauses (in `CREATE` `TABLE`) for more information on this clause.

Restriction on Mapping Tables You cannot specify `NOMAPPING` if any bitmapped indexes have been defined on `table`.

key_compression Use the `key_compression` clause to enable or disable key compression in an index-organized table.

- `COMPRESS` enables key compression, which eliminates repeated occurrence of primary key column values in index-organized tables. Use `integer` to specify the prefix length (number of prefix columns to compress).

  The valid range of prefix length values is from 1 to the number of primary key columns minus 1. The default prefix length is the number of primary key columns minus 1.

- `NOCOMPRESS` disables key compression in index-organized tables. This is the default.

TABLESPACE tablespace Specify the tablespace into which the rebuilt index-organized table is to be stored.

Restrictions on Moving Tables Moving tables is subject to the following restrictions:

- If you specify `MOVE`, then it must be the first clause in the `ALTER` `TABLE` statement, and the only clauses outside this clause that are allowed are the `physical_attributes_clause`, the `parallel_clause`, and the `LOB_storage_clause`.

- You cannot move a table containing a `LONG` or `LONG` `RAW` column.

- You cannot `MOVE` an entire partitioned table (either heap or index organized). You must move individual partitions or subpartitions.

Notes Regarding LOBs: For any LOB columns you specify in a `move_table_clause`: Oracle Database drops the old LOB data segment and corresponding index segment and creates new segments, even if you do not specify a new tablespace. If the LOB index in `table` resided in a different tablespace from the LOB data, then Oracle Database collocates the LOB index in the same tablespace with the LOB data after the move.

See Also: move_table_partition and move_table_subpartition

enable_disable_clause

The `enable_disable_clause` lets you specify whether and how Oracle Database should apply an integrity constraint. The `DROP` and `KEEP` clauses are valid only when you are disabling a unique or primary key constraint.

See Also: The enable_disable_clause (in `CREATE` `TABLE`) for a complete description of this clause, including notes and restrictions that relate to this statement "Using Indexes to Enforce Constraints" for information on using indexes to enforce constraints

TABLE LOCK

Oracle Database permits DDL operations on a table only if the table can be locked during the operation. Such table locks are not required during DML operations.

Note: Table locks are not acquired on temporary tables.

ENABLE TABLE LOCK Specify `ENABLE` `TABLE` `LOCK` to enable table locks, thereby allowing DDL operations on the table. All currently executing transactions must commit or roll back before Oracle Database enables the table lock.

Caution: Oracle Database waits until active DML transactions in the database have completed before locking the table. Sometimes the resulting delay is considerable.

DISABLE TABLE LOCK Specify `DISABLE` `TABLE` `LOCK` to disable table locks, thereby preventing DDL operations on the table.

ALL TRIGGERS

Use the `ALL` `TRIGGERS` clause to enable or disable all triggers associated with the table.

ENABLE ALL TRIGGERS Specify `ENABLE` `ALL` `TRIGGERS` to enable all triggers associated with the table. Oracle Database fires the triggers whenever their triggering condition is satisfied.

To enable a single trigger, use the `enable_clause` of `ALTER` `TRIGGER`.

See Also: CREATE TRIGGER, ALTER TRIGGER, and "Enabling Triggers: Example"

DISABLE ALL TRIGGERS Specify `DISABLE` `ALL` `TRIGGERS` to disable all triggers associated with the table. Oracle Database does not fire a disabled trigger even if the triggering condition is satisfied.

Examples

Collection Retrieval: Example The following statement modifies nested table column `ad_textdocs_ntab` in the sample table `sh.print_media` so that when queried it returns actual values instead of locators:

    ALTER TABLE print_media MODIFY NESTED TABLE ad_textdocs_ntab
       RETURN AS VALUE;

Specifying Parallel Processing: Example The following statement specifies parallel processing for queries to the sample table `oe.customers`:

    ALTER TABLE customers
       PARALLEL;

Changing the State of a Constraint: Examples The following statement places in `ENABLE` `VALIDATE` state an integrity constraint named `emp_manager_fk` in the `employees` table:

    ALTER TABLE employees
       ENABLE VALIDATE CONSTRAINT emp_manager_fk
       EXCEPTIONS INTO exceptions;

Each row of the `employees` table must satisfy the constraint for Oracle Database to enable the constraint. If any row violates the constraint, then the constraint remains disabled. The database lists any exceptions in the table `exceptions`. You can also identify the exceptions in the `employees` table with the following statement:

    SELECT e.*
       FROM employees e, exceptions ex
       WHERE e.rowid = ex.row_id
          AND ex.table_name = 'EMPLOYEES'
          AND ex.constraint = 'EMP_MANAGER_FK';

The following statement tries to place in `ENABLE` `NOVALIDATE` state two constraints on the `employees` table:

    ALTER TABLE employees
       ENABLE NOVALIDATE PRIMARY KEY
       ENABLE NOVALIDATE CONSTRAINT emp_last_name_nn;

This statement has two `ENABLE` clauses:

- The first places a primary key constraint on the table in `ENABLE` `NOVALIDATE` state.

- The second places the constraint named `emp_last_name_nn` in `ENABLE` `NOVALIDATE` state.

In this case, Oracle Database enables the constraints only if both are satisfied by each row in the table. If any row violates either constraint, then the database returns an error and both constraints remain disabled.

Consider the foreign key constraint on the `location_id` column of the `departments` table, which references the primary key of the `locations` table. The following statement disables the primary key of the `locations` table:

    ALTER TABLE locations
       MODIFY PRIMARY KEY DISABLE CASCADE;

The unique key in the `locations` table is referenced by the foreign key in the `departments` table, so you must specify `CASCADE` to disable the primary key. This clause disables the foreign key as well.

Creating an Exceptions Table for Index-Organized Tables: Example The following example creates the `except_table` table to hold rows from the index-organized table `hr.countries` that violate the primary key constraint:

    EXECUTE DBMS_IOT.BUILD_EXCEPTIONS_TABLE ('hr', 'countries', 'except_table');

    ALTER TABLE countries
       ENABLE PRIMARY KEY
       EXCEPTIONS INTO except_table;

To specify an exception table, you must have the privileges necessary to insert rows into the table. To examine the identified exceptions, you must have the privileges necessary to query the exceptions table.

See Also: INSERT and SELECT for information on the privileges necessary to insert rows into tables

Disabling a CHECK Constraint: Example The following statement defines and disables a `CHECK` constraint on the `employees` table:

    ALTER TABLE employees ADD CONSTRAINT check_comp
       CHECK (salary + (commission_pct*salary) <= 5000)
       DISABLE;

The constraint `check_comp` ensures that no employee's total compensation exceeds $5000. The constraint is disabled, so you can increase an employee's compensation above this limit.

Enabling Triggers: Example The following statement enables all triggers associated with the `employees` table:

    ALTER TABLE employees
       ENABLE ALL TRIGGERS;

Deallocating Unused Space: Example The following statement frees all unused space for reuse in table `employees`, where the high water mark is above `MINEXTENTS`:

    ALTER TABLE employees
        DEALLOCATE UNUSED;

Renaming a Column: Example The following example renames the `credit_limit` column of the sample table `oe.customers` to `credit_amount`:

    ALTER TABLE customers
       RENAME COLUMN credit_limit TO credit_amount;

Dropping a Column: Example This statement illustrates the `drop_column_clause` with `CASCADE` `CONSTRAINTS`. Assume table `t1` is created as follows:

    CREATE TABLE t1 (
       pk NUMBER PRIMARY KEY,
       fk NUMBER,
       c1 NUMBER,
       c2 NUMBER,
       CONSTRAINT ri FOREIGN KEY (fk) REFERENCES t1,
       CONSTRAINT ck1 CHECK (pk > 0 and c1 > 0),
       CONSTRAINT ck2 CHECK (c2 > 0)
    );

An error will be returned for the following statements:

    /* The next two statements return errors:
    ALTER TABLE t1 DROP (pk);  -- pk is a parent key
    ALTER TABLE t1 DROP (c1);  -- c1 is referenced by multicolumn
                               -- constraint ck1

Submitting the following statement drops column `pk`, the primary key constraint, the foreign key constraint, `ri`, and the check constraint, `ck1`:

    ALTER TABLE t1 DROP (pk) CASCADE CONSTRAINTS;

If all columns referenced by the constraints defined on the dropped columns are also dropped, then `CASCADE` `CONSTRAINTS` is not required. For example, assuming that no other referential constraints from other tables refer to column `pk`, then it is valid to submit the following statement without the `CASCADE` `CONSTRAINTS` clause:

    ALTER TABLE t1 DROP (pk, fk, c1);

Modifying Index-Organized Tables: Examples This statement modifies the `INITRANS` parameter for the index segment of index-organized table `countries_demo`, which is based on `hr.countries`:

    ALTER TABLE countries_demo INITRANS 4;

The following statement adds an overflow data segment to index-organized table `countries`:

    ALTER TABLE countries_demo ADD OVERFLOW;

This statement modifies the `INITRANS` parameter for the overflow data segment of index-organized table `countries`:

    ALTER TABLE countries_demo OVERFLOW INITRANS 4;

Splitting Table Partitions: Examples The following statement splits the old partition `sales_q4_2000` in the sample table `sh.sales`, creating two new partitions, naming one `sales_q4_2000b` and reusing the name of the old partition for the other:

    ALTER TABLE sales SPLIT PARTITION SALES_Q4_2000
       AT (TO_DATE('15-NOV-2000','DD-MON-YYYY'))
       INTO (PARTITION SALES_Q4_2000, PARTITION SALES_Q4_2000b);

Assume that the sample table `pm.print_media` was range partitioned into partitions p1 and p2. (You would have to convert the `LONG` column in `print_media` to LOB before partitioning the table.) The following statement splits partition `p2` of that table into partitions `p2a` and `p2b`:

    ALTER TABLE print_media_part
       SPLIT PARTITION p2 AT (150) INTO
       (PARTITION p2a TABLESPACE omf_ts1
          LOB ad_photo, ad_composite) STORE AS (TABLESPACE omf_ts2),
       PARTITION p2b
          LOB (ad_photo, ad_composite) STORE AS (TABLESPACE omf_ts2));

In both partitions `p2a` and `p2b`, Oracle Database creates the LOB segments for columns `ad_photo` and `ad_composite` in tablespace `omb_ts2`. The LOB segments for the remaining columns in partition p2a are stored in tablespace omf_ts1. The LOB segments for the remaining columns in partition p2b remain in the tablespaces in which they resided prior to this `ALTER` statement. However, the database creates new segments for all the LOB data and LOB index segments, even if they are not moved to a new tablespace.

Adding a Table Partition with a LOB: Examples The following statement adds a partition `p3` to the `print_media_part` table (see preceding example) and specifies storage characteristics for the `BLOB` and `CLOB` columns of that table:

    ALTER TABLE print_media_part ADD PARTITION p3 VALUES LESS THAN (MAXVALUE)
       LOB (ad_photo, ad_composite) STORE AS (TABLESPACE omf_ts2)
       LOB (ad_sourcetext, ad_finaltext) STORE AS (TABLESPACE omf_ts1);

The LOB data and LOB index segments for columns `ad_photo` and `ad_composite` in partition `p3` will reside in tablespace `omf_ts2`. The remaining attributes for these LOB columns will be inherited first from the table-level defaults, and then from the tablespace defaults.

The LOB data segments for columns `ad_source_text` and `ad_finaltext` will reside in the `omf_ts1` tablespace, and will inherit all other attributes from the table-level defaults and then from the tablespace defaults.

Working with Default List Partitions: Example The following statements use the list partitioned table created in "List Partitioning Example". The first statement splits the existing default partition into a new `south` partition and a default partition:

    ALTER TABLE list_customers SPLIT PARTITION rest
       VALUES ('MEXICO', 'COLOMBIA')
       INTO (PARTITION south, PARTITION rest);

The next statement merges the resulting default partition with the `asia` partition:

    ALTER TABLE list_customers
       MERGE PARTITIONS asia, rest INTO PARTITION rest;

The next statement re-creates the `asia` partition by splitting the default partition:

    ALTER TABLE list_customers SPLIT PARTITION rest
       VALUES ('CHINA', 'THAILAND')
       INTO (PARTITION asia, partition rest);

Merging Two Table Partitions: Example The following statement merges back into one partition the partitions created in "Splitting Table Partitions: Examples":

    ALTER TABLE sales
       MERGE PARTITIONS sales_q4_2000, sales_q4_2000b
       INTO PARTITION sales_q4_2000;

Dropping a Table Partition: Example The following statement drops partition `p3` created in "Adding a Table Partition with a LOB: Examples":

    ALTER TABLE print_media_part DROP PARTITION p3;

Exchanging Table Partitions: Example It creates `exchange_table` with the same structure as the partitions of the `list_customers` table created in "List Partitioning Example". It then replaces partition `rest` with table `exchange_table` without exchanging local index partitions with corresponding indexes on `exchange_table` and without verifying that data in `exchange_table` falls within the bounds of partition `rest`:

    CREATE TABLE exchange_table (
       customer_id     NUMBER(6),
       cust_first_name VARCHAR2(20),
       cust_last_name  VARCHAR2(20),
       cust_address    CUST_ADDRESS_TYP,
       nls_territory   VARCHAR2(30),
       cust_email      VARCHAR2(30));

    ALTER TABLE list_customers
       EXCHANGE PARTITION feb97 WITH TABLE sales_feb97
       WITHOUT VALIDATION;

Modifying Table Partitions: Examples The following statement marks all the local index partitions corresponding to the `asia` partition of the `list_customers` table `UNUSABLE`:

    ALTER TABLE list_customers MODIFY PARTITION asia
       UNUSABLE LOCAL INDEXES;

The following statement rebuilds all the local index partitions that were marked `UNUSABLE`:

    ALTER TABLE list_customers MODIFY PARTITION asia
       REBUILD UNUSABLE LOCAL INDEXES;

Moving Table Partitions: Example The following statement moves partition `p2b` (from "Splitting Table Partitions: Examples") to tablespace `omf_ts1`:

    ALTER TABLE print_media_part
       MOVE PARTITION p2b TABLESPACE omf_ts1;

Renaming Table Partitions: Examples The following statement renames a partition of the `sh.sales` table:

    ALTER TABLE sales RENAME PARTITION sales_q4_2003 TO sales_currentq;;

Truncating Table Partitions: Example The following statement uses the `print_media_demo` table created in "Partitioned Table with LOB Columns Example". It deletes all the data in the `p1` partition and deallocates the freed space:

    ALTER TABLE print_media_demo
       TRUNCATE PARTITION p1 DROP STORAGE;

Updating Global Indexes: Example The following statement splits partition `sales_q1_2000` of the sample table `sh.sales` and updates any global indexes defined on it:

    ALTER TABLE sales SPLIT PARTITION sales_q1_2000
       AT (TO_DATE('16-FEB-2000','DD-MON-YYYY'))
       INTO (PARTITION q1a_2000, PARTITION q1b_2000)
       UPDATE GLOBAL INDEXES;

Specifying Object Identifiers: Example The following statements create an object type, a corresponding object table with a primary-key-based object identifier, and a table having a user-defined `REF` column:

    CREATE TYPE emp_t AS OBJECT (empno NUMBER, address CHAR(30));

    CREATE TABLE emp OF emp_t (
       empno PRIMARY KEY)
       OBJECT IDENTIFIER IS PRIMARY KEY;

    CREATE TABLE dept (dno NUMBER, mgr_ref REF emp_t SCOPE is emp);

The next statements add a constraint and a user-defined `REF` column, both of which reference table `emp`

    ALTER TABLE dept ADD CONSTRAINT mgr_cons FOREIGN KEY (mgr_ref)
       REFERENCES emp;
    ALTER TABLE dept ADD sr_mgr REF emp_t REFERENCES emp;

Adding a Table Column: Example The following statement adds a column named `duty_pct` of datatype `NUMBER` and a column named `visa_needed` of datatype `VARCHAR2` with a size of 3 and a `CHECK` integrity constraint:

    ALTER TABLE countries
       ADD (duty_pct     NUMBER(2,2)  CHECK (duty_pct < 10.5),
            visa_needed  VARCHAR2(3));

Modifying Table Columns: Examples The following statement increases the size of the `duty_pct` column:

    ALTER TABLE countries
       MODIFY (duty_pct NUMBER(3,2));

Because the `MODIFY` clause contains only one column definition, the parentheses around the definition are optional.

The following statement changes the values of the `PCTFREE` and `PCTUSED` parameters for the `employees` table to 30 and 60, respectively:

    ALTER TABLE employees
       PCTFREE 30
       PCTUSED 60;

Data Encryption: Examples The following statement encrypts the salary column of the `hr.employees` table using the encryption algorithm `3DES168`. As described in "Semantics" above, you must first enable transparent data encryption:

    ALTER TABLE employees    MODIFY (salary ENCRYPT USING '3DES168');

The following statement adds a new encrypted column `online_acct_pw` to the `oe.customers` table.

    ALTER TABLE customers
       ADD (online_acct_pw VARCHAR2(8) ENCRYPT);

The following example decrypts the customer.online_acct_pw column:

    ALTER TABLE customers
       MODIFY (online_acct_pw DECRYPT;

Allocating Extents: Example The following statement allocates an extent of 5 kilobytes for the `employees` table and makes it available to instance 4:

    ALTER TABLE employees
      ALLOCATE EXTENT (SIZE 5K INSTANCE 4);

Because this statement omits the `DATAFILE` parameter, Oracle Database allocates the extent in one of the datafiles belonging to the tablespace containing the table.

Specifying Default Column Value: Examples This statement modifies the `min_price` column of the `product_information` table so that it has a default value of 10:

    ALTER TABLE product_information
      MODIFY (min_price DEFAULT 10);

If you subsequently add a new row to the `product_information` table and do not specify a value for the `min_price` column, then the value of the `min_price` column is automatically 0:

    INSERT INTO product_information (product_id, product_name,
       list_price)
       VALUES (300, 'left-handed mouse', 40.50);

    SELECT product_id, product_name, list_price, min_price
        FROM product_information
        WHERE product_id = 300;

    PRODUCT_ID PRODUCT_NAME         LIST_PRICE  MIN_PRICE
    ---------- -------------------- ---------- ----------
           300 left-handed mouse          40.5         10

To discontinue previously specified default values, so that they are no longer automatically inserted into newly added rows, replace the values with `NULL`, as shown in this statement:

    ALTER TABLE product_information
       MODIFY (min_price DEFAULT NULL);

The `MODIFY` clause need only specify the column name and the modified part of the definition, rather than the entire column definition. This statement has no effect on any existing values in existing rows.

Adding a Constraint to an XMLType Table: Example The following example adds a primary key constraint to the `xwarehouses` table, created in "XMLType Examples":

    ALTER TABLE xwarehouses
       ADD (PRIMARY KEY(XMLDATA."WarehouseID"));

Please refer to XMLDATA Pseudocolumn for information about this pseudocolumn.

Renaming Constraints: Example The following statement renames the `cust_fname_nn` constraint on the sample table `oe.customers` to `cust_firstname_nn`:

    ALTER TABLE customers RENAME CONSTRAINT cust_fname_nn
       TO cust_firstname_nn;

Dropping Constraints: Examples The following statement drops the primary key of the `departments` table:

    ALTER TABLE departments
        DROP PRIMARY KEY CASCADE;

If you know that the name of the `PRIMARY` `KEY` constraint is `pk_dept`, then you could also drop it with the following statement:

    ALTER TABLE departments
        DROP CONSTRAINT pk_dept CASCADE;

The `CASCADE` clause causes Oracle Database to drop any foreign keys that reference the primary key.

The following statement drops the unique key on the `email` column of the `employees` table:

    ALTER TABLE employees
        DROP UNIQUE (email);

The `DROP` clause in this statement omits the `CASCADE` clause. Because of this omission, Oracle Database does not drop the unique key if any foreign key references it.

LOB Columns: Examples The following statement adds `CLOB` column `resume` to the `employee` table and specifies LOB storage characteristics for the new column:

    ALTER TABLE employees ADD (resume CLOB)
      LOB (resume) STORE AS resume_seg (TABLESPACE example);

To modify the LOB column `resume` to use caching, enter the following statement:

    ALTER TABLE employees MODIFY LOB (resume) (CACHE);

Nested Tables: Examples The following statement adds the nested table column `skills` to the `employee` table:

    ALTER TABLE employees ADD (skills skill_table_type)
        NESTED TABLE skills STORE AS nested_skill_table;

You can also modify nested table storage characteristics. Use the name of the storage table specified in the `nested_table_col_properties` to make the modification. You cannot query or perform DML statements on the storage table. Use the storage table only to modify the nested table column storage characteristics.

The following statement creates table `vet_service` with nested table column `client` and storage table `client_tab`. Nested table `client_tab` is modified to specify constraints:

    CREATE TYPE pet_t AS OBJECT
       (pet_id NUMBER, pet_name VARCHAR2(10), pet_dob DATE);
    /

    CREATE TYPE pet AS TABLE OF pet_t;
    /

    CREATE TABLE vet_service (vet_name VARCHAR2(30),
                              client   pet)
      NESTED TABLE client STORE AS client_tab;

    ALTER TABLE client_tab ADD UNIQUE (pet_id);

The following statement alters the storage table for a nested table of `REF` values to specify that the `REF` is scoped:

    CREATE TYPE emp_t AS OBJECT (eno number, ename char(31));
    CREATE TYPE emps_t AS TABLE OF REF emp_t;
    CREATE TABLE emptab OF emp_t;
    CREATE TABLE dept (dno NUMBER, employees emps_t)
       NESTED TABLE employees STORE AS deptemps;
    ALTER TABLE deptemps ADD (SCOPE FOR (COLUMN_VALUE) IS emptab);

Similarly, to specify storing the `REF` with rowid:

    ALTER TABLE deptemps ADD (REF(column_value) WITH ROWID);

In order to execute these `ALTER` `TABLE` statements successfully, the storage table `deptemps` must be empty. Also, because the nested table is defined as a table of scalar values (`REF` values), Oracle Database implicitly provides the column name `COLUMN_VALUE` for the storage table.

See Also: CREATE TABLE for more information about nested table storage Oracle Database Application Developer's Guide - Fundamentals for more information about nested tables

REF Columns: Examples The following statement creates an object type `dept_t` and then creates table `staff`:

    CREATE TYPE dept_t AS OBJECT
       (deptno NUMBER, dname VARCHAR2(20));
    /

    CREATE TABLE staff
       (name   VARCHAR(100),
        salary NUMBER,
        dept   REF dept_t);

An object table `offices` is created as:

    CREATE TABLE offices OF dept_t;

The `dept` column can store references to objects of `dept_t` stored in any table. If you would like to restrict the references to point only to objects stored in the `departments` table, then you could do so by adding a scope constraint on the `dept` column as follows:

    ALTER TABLE staff
        ADD (SCOPE FOR (dept) IS offices);

The preceding `ALTER` `TABLE` statement will succeed only if the `staff` table is empty.

If you want the `REF` values in the `dept` column of `staff` to also store the rowids, issue the following statement:

    ALTER TABLE staff
       ADD (REF(dept) WITH ROWID);

Additional Examples For examples of defining integrity constraints with the `ALTER` `TABLE` statement, see the constraint.

For examples of changing the storage parameters of a table, see the storage_clause .